SQL SERVER, Composite Primary Key Handling

Question

I have a table with 2 primary keys (int, int) and was wondering if it was possible to have one of the primary keys set to auto increment and the other not? The following is the basics of the table structure:

Table
{
   Id - Int,
   VersionId - Int
}

Basically I have an Id with a Version so that there is a complete history of that record and therefor can be rolled back at any time.

I want to be able to insert and Id and for it to then automatically handle the version number for me. Is this possible?

Thanks in advance.

Answer 1

You can create the compound PK but the version incrementing won't work as you expect - it will be continuous and not 'reset' to version 1 on every new ID

create table SomeTable
(
  id int not null,
  version int identity(1,1) not null,
  primary key(id, version)
)

insert into SomeTable(id) values (1234)
insert into SomeTable(id) values (1235)
insert into SomeTable(id) values (1234)

select * from SomeTable

Returns

1234 1
1235 2
1234 3

etc

Answer 2

No, SQL Server has no such "partitioned" identity concept. If you really need this (really??) then you have to provide this programmatically, somehow (from your client, or by handling it yourself in SQL Server using a helper table or something like that).

You can have an IDENTITY field - but that's a consecutive INT number over your whole table - not consecutive for each Id. On the other hand, since that IDENTITY will be unique and ever-increasing, you can use that as a version field, too (it's not going to be 1, 2, 3 for each ID - but the sequence is still there).

In that case, you might have

ID    Version
 1        1
 2        2
 3        3
 1        4

and so forth.