SQL 2008 Composite Primary Key With One Part of the Key Auto Increment

Simple table with composite primary key.

PK1 smallint, PK2 smallint auto increment, strValue varchar(20) not null.

This works but PK2 increments independent of PK1.

What I would like is for PK2 to start fresh with a fresh PK1.

Here is what I get

    1, 1, 1a
    1, 2, 1b
    2, 3, 2a
    2, 4, 2b

What I want

    1, 1, 1a
    1, 2, 1b
    2, 1, 2a
    2, 2, 2b 

I take it I need to remove the auto increment from PK2 and generate the proper value on the Insert statement? Is that the correct way to get there and do you recommend syntax for the Insert statement? Auto increment 开发者_StackOverflowPK1 is not an option as it is part of FK relationship.

Assume I need to insert 2, x, 3b and need to know what value was assigned for x.

There are NOT going to be a lot of inserts (like 100 / day). There is a unique constraint on PK1, strValue.

To be clear I think SQL 2008 is doing the right thing and am not suggesting that SQL should behave this way by default.

If you're not doing a lot of inserts to the table you can probably remove the identity column and simply do:

IF NOT EXISTS ( SELECT * FROM table WHERE PK1 = 2 AND strValue = '3b' )
BEGIN
  DECLARE @next_PK2 INT

  SELECT
    @next_PK2 = ISNULL(MAX(PK2) + 1, 0) + 1
  FROM
    table
  WHERE
    PK1 = 2

  INSERT table VALUES (2, @next_PK2, '3b')
END

Yes, if you want to control the incremental value of PK2 you need to generate that during insert (such as with a ROW_NUMBER(). There isn't any other way to set the PK2 independently.