ID Number: Q75164
1.10 1.11 4.20
OS/2
Summary:
It is sometimes necessary for a table to have a unique key value for
every row. This is usually some type of ID column. When that unique
value is not related to the data in the row, this can present a
challenge.
What follows is a method for creating a unique value for each row.
These values are generated as each new row is inserted, and can be
implemented in a stored procedure or a trigger. Note that this method
guarantees unique values in a multi-user environment.
More Information:
This method 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 this table simultaneously, it is
necessary 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 core 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
The above transaction works as follows. 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 COMMIT
TRANSACTION, which releases all locks and allows the next user to
access the MAX_ID value.
Below is a variation of this that minimizes time inside the BEGIN
TRANSACTION..COMMIT TRANSACTION, thereby also minimizing the length of
time that page locks are held. Note that with this transaction
there is the risk of creating "holes" in the list of unique values.
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
Additional reference words: Optimization and tuning