T-SQL Unique Identity Column as Part of Composite Primary Key

Question

Suppose that you have a table with the following structure:

CREATE TABLE [log] (
  [type] int NOT NULL,
  [stat] nvarchar(20) NOT NULL,
  [id] int IDENTITY (1, 1) NOT NULL,
  descr nvarchar(20),
  PRIMARY KEY ([type], [stat], [id])
)

Is it possible to force the [id] to be incremented only whenever the other two PK fields have the same values, and not independently as is now? For instance:

type    stat      id     descr
5       ERROR     1      Test  <---
3       WARNING   1      Test
5       ERROR     2      Test  <---
2       ERROR     1      Test
1       WARNING   1      Test
5       WARNING   1      Test
5       ERROR     3      Test  <---

Answer 1

No. The purpose of an IDENTITY (or SEQUENCE) is only to generate an incremental integer. There may be gaps as values are not reused, and values may be reserved but not used.

You can use an expression in queries to show the desired value.

ROW_NUMBER() OVER (PARTITION BY type, stat ORDER BY id) AS Seq