INF: Implementing a Unique, Increasing Key Value (75164)

MORE INFORMATION

If you use this method, you must use a table to store the current maximum value.

Create this table using:

   CREATE TABLE MAX_ID_TABLE (MAX_ID INT)
				

This MAX_ID_TABLE needs only one row in it. Add that one row with an initial value of zero using:

   INSERT INTO MAX_ID_TABLE (MAX_ID) VALUES (0)
				

In a single-user environment, the solution is simple; one user just increments this maximum value, then uses that value in the next row to be inserted. However, SQL Server is a multi-user database. With many users accessing and updating the table simultaneously, you need to guarantee that no two users obtain the same maximum value.

This method takes advantage of the locking mechanisms of SQL Server to ensure that each user obtains a unique key value. When a user does a BEGIN TRANSACTION, SQL Server maintains all page locks until the COMMIT TRANSACTION is performed. The basis of this method is the following transaction:

   BEGIN TRANSACTION
      UPDATE MAX_ID_TABLE SET MAX_ID = MAX_ID + 1
      DECLARE @MAX INT
      SELECT @MAX = MAX_ID FROM MAX_ID_TABLE
      INSERT INTO INFO_TABLE (UNIQUE_ID,...)
         VALUES (@MAX,...)
   COMMIT TRANSACTION
				

In the transaction listed above, immediately following the BEGIN TRANSACTION is an UPDATE statement. This UPDATE gets an exclusive lock on the page that contains the current MAX_ID, and increments the value in one step. This exclusive lock keeps all other users from reading or updating this page (and thus MAX_ID), and is maintained until the COMMIT TRANSACTION. Then it declares a local variable @MAX, and stores the new unique value in that variable using a SELECT statement. The local variable is then used to INSERT the new row with the unique key value. The final step is to issue the COMMIT TRANSACTION, which releases all locks and allows the next user to access the MAX_ID value.

Below is a variation of this transaction that minimizes the time inside the BEGIN TRANSACTION..COMMIT TRANSACTION, thereby also minimizing the length of time that page locks are held. Note that with this transaction, the risk of creating "holes" in the list of unique values exists. This occurs when the UPDATE succeeds in incrementing the maximum value but the INSERT fails.

   BEGIN
      DECLARE @MAX INT
      BEGIN TRANSACTION
         UPDATE MAX_ID_TABLE SET MAX_ID = MAX_ID + 1
         SELECT @MAX = MAX_ID FROM MAX_ID_TABLE
      COMMIT TRANSACTION
      INSERT INTO INFO_TABLE (UNIQUE_ID,...)
         VALUES (@MAX,...)
   END