title: T-SQL Unique Identity Column as Part of Composite Primary Key
body: <p>Suppose that you have a table with the following structure:</p>
  
  <pre><code>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])
  )
  </code></pre>
  
  <p>Is it possible to force the <code>[id]</code> to be incremented only whenever the other two PK fields have the same values, and not independently as is now?  For instance:</p>
  
  <pre><code>type    stat      id     descr
  5       ERROR     1      Test  &lt;---
  3       WARNING   1      Test
  5       ERROR     2      Test  &lt;---
  2       ERROR     1      Test
  1       WARNING   1      Test
  5       WARNING   1      Test
  5       ERROR     3      Test  &lt;---
  </code></pre>