Create incrementing number based on another column to form two-part key

I have a table in a SQL Server database. It has a two-part natural key on Location (an integer) and CaseNumber (also an integer). When I create a new record I want to specify the Location and have the CaseNumber generated for me so that for any one location all Case Numbers are unique, incrementing and without gaps. Records in the table will never be deleted.开发者_开发问答

What would be the best way to do this ensuring that it is safe for concurrent updates?

---

This has been covered before, but here are some general ideas:

Add a UNIQUE INDEX covering Location, CaseNumber. This will prevent dupes should your concurrency checks fail.

Encapsulate your code for checking/inserting the new max value in a transaction. It's not pretty but its how you can insure there won't be issues with concurrency.

i.e...

DECLARE @Max int
BEGIN TRAN
SET @Max = (SELECT MAX(CaseNumber) FROM MyTable WITH (TABLOCK) 
            WHERE Location = @Location) + 1

INSERT INTO MyTable
@Location, @Max

COMMIT TRAN