Sql Server Custom Identity Column

I want to generate a custom identity column related to type of product. Can this query guaranty the order of identity and resolve concurrency. This is a sample query: BEGIN TRAN

Solution 1:

Try this:

BEGIN TRAN

INSERTINTO TBLKEY
VALUES((SELECTMAX(ID) +1AS NVARCHAR) FROM TBLKEY WITH (UPDLOCK)),'EHSAN')

COMMIT

When selecting the max ID you acquire a U lock on the row. The U lock is incompatible with the U lock which will try to acquire another session with the same query running at the same time. Only one query will be executed at a given time. The ids will be in order and continuous without any gaps between them.

A better solution would be to create an extra table dedicated only for storing the current or next id and use it instead of the maximum.

You can understand the difference by doing the following:

Prepare a table

CREATETABLE T(id intnotnullPRIMARY KEY CLUSTERED)
INSERTINTO T VALUES(1)

And then run the following query in two different sessions one after another with less than 10 seconds apart

BEGIN TRAN
DECLARE@idvintSELECT@idv=max (id) FROM T
WAITFOR DELAY '0:0:10'INSERTINTO T VALUES(@idv+1)
COMMIT

Wait for a while until both queries complete. Observe that one of them succeeded and the other failed.

Now do the same with the following query

BEGIN TRAN
DECLARE@idvintSELECT@idv=max (id) FROM T WITH (UPDLOCK)
WAITFOR DELAY '0:0:5'INSERTINTO T VALUES(@idv+1)
COMMIT

View the contents of T

Cleanup the T Table with DROP TABLE T

Solution 2:

This would be a bad thing to do as there is no way to guarantee that two queries running at the same time wouldn't get MAX(ID) as being the same value.

If you used a standard identity column you could also have a computed column which uses that or just return the key when you return the data.

Ed