Auto Increment Key Pair with Start Digit at 200000 SQL SERVER 2008 I'm currently rewriting a front end for a program that handles interviews. I want to prevent an issue the older program had were during the tiny amount of time it was doing a lookup for the next control number, another client could get the same number and create a conflict. I think it's best to do this in the database during the initial insert rather than the code.

The way I want it to work is with a two part key were one part is a Client's abbreviated name and the other part is an auto increment integer that starts at 200000. I would like it to do the 200000+ part per client. I want to auto fill the columns instead of having the program do it so there isn't any way the program can create a duplicate Client Key. It would be awesome if I could use this as the primary key(s). I have to leave it in two columns due to management.

Client1    200000
Client1    200001
Client2    200000
Client1    200002
Client2    200001
Client1    200003


How would I go about doing this?
Here is one way, note that this effectively limits concurrency, so don't expect stellar performance at scale.

INSERT dbo.TableName(col1, col2)
  SELECT @ClientName, COALESCE(
    (SELECT MAX(col2) + 1 
     FROM dbo.TableName WITH (UPDLOCK, HOLDLOCK)
     WHERE col1 = @ClientName), 200000);


Of course if anyone ever deletes any rows from this table, you'll be missing values. And if an insert gets rolled back, that rolled back number will be reused, so be careful if you reveal that assigned value anywhere prior to the rollback.