Handling Sequences

SQL Server 2000 and earlier releases provide the IDENTITY column property as a tool for automatically generating numbers. You might need SQL Server to generate numbers automatically for unique keys such as order IDs, invoice IDs, and so on. However, sometimes you need to create unique keys that span tables (e.g., invoices for different types of customers might need to be stored in different tables). You can't use the IDENTITY property in such cases because IDENTITY is table-dependent, i.e., its scope is a specific table and not the whole database. You need a sequence that you can increment whenever you want and use the sequence values as keys that can be stored in different tables. Unfortunately, SQL Server 2005 doesn't introduce an independent auto-numbering tool. However, using ranking window function enhancements that I've discussed in Calculating Row Numbers in SQL Server 2005, you can develop a simple, highly efficient custom sequence tool.

Handling Single-Row Inserts

Suppose you have two tables in which you store invoices-Invoices1 and Invoices2, which you can create by running the code that Listing 1 shows. Every time you need to insert a row for a new invoice into either of the tables, you want to increment a sequence value and use it as the new invoice ID. Invoice IDs must be unique and must all be accounted for-that is, you can't permit a skipped invoice ID. You might have seen solutions for maintaining a custom sequence for single row-inserts in public technical forums or elsewhere; let's briefly look at such a solution. You create a table to hold the last-used sequence value by running the following code:

CREATE TABLE SeqInv(val int NOT NULL)INSERT INTO SeqInv VALUES(0)

Populating the sequence table with an initial value of 0 ensures that the first invoice ID generated will be 1. To insert a new invoice row into the Invoices1 table, run the following code:

BEGIN TRAN  DECLARE @invid AS int  UPDATE SeqInv SET @invid = val = val+1  INSERT INTO Invoices1(invid, dt) VALUES(@invid, '20050101')COMMIT

This code declares the variable @invid, then invokes an UPDATE statement that increments the val column's value in the SeqInv table by 1 and stores the incremented value in the @invid variable.

The code uses the newly generated invoice ID in the INSERT statement following the UPDATE . Both incrementing the sequence and inserting the new invoice into the Invoices1 table happen in a single transaction. Conflicts can't occur, because SQL Server locks the sequence as soon as the UPDATE touches the row and keeps it locked until the end of the transaction, so all requests for new invoice IDs are queued. If for any reason the transaction rolls back, the invoice isn't inserted and the sequence value reverts to its original state before the transaction started. Run the following code to insert a new invoice to the Invoices2 table:

BEGIN TRAN  DECLARE @invid AS int  UPDATE SeqInv SET @invid = val = val+1  INSERT INTO Invoices2(invid, dt) VALUES(@invid, '20050101')COMMIT

The invoice inserted into Invoices1 got the invoice ID 1, and the invoice inserted into Invoices2 got the invoice ID 2 from the same sequence. At this point, the current value of the sequence is 2, which is the last-assigned sequence value.

To automate the assigning of sequence values, you create INSTEAD OF INSERT triggers on all target tables that increment the sequence value and use it as the new invoice ID. Run the code that Listing 2 shows to create the triggers on the Invoices1 and Invoices2 tables.

The trigger code deals only with single-row inserts. If 0 rows are affected, the trigger simply aborts. If more than one row is affected, the trigger rolls back. If exactly one row is affected, the code in the trigger increments the sequence value and uses it as the new invoice ID. Run the following code to insert rows in both invoice tables without specifying invoice IDs, just values for all other columns:

INSERT INTO Invoices1(dt) VALUES('20050102')INSERT INTO Invoices2(dt) VALUES('20050102')

Query both tables, and you'll see a new invoice that has ID 3 in the Invoices1 table and one with ID 4 in the Invoices2 table.

Handling Multi-Row Inserts

The really tough part of providing a custom sequence solution comes when you want to allow multi-row inserts. You might need to populate the invoice tables with data that resulted from a query. Of course, you can alter the trigger such that its code would loop through the rows in the inserted table (using a temporary table and a loop or a cursor), assigning a new invoice ID in each iteration. But such an inefficient solution would slow down the inserts significantly.

You can devise a highly efficient solution by utilizing the ROW_NUMBER() function Microsoft introduced in SQL Server 2005. (For information about ROW_NUMBER(), see "Calculating Row Numbers in SQL Server 2005," April 2004, InstantDoc ID 42302.) Within the trigger, store the current sequence value in the @invid variable and increment the sequence by the number of affected rows:

UPDATE SeqInv SET @invid = val, val = val+@rc

Next, issue the following INSERT statement, which calculates row numbers for all rows from inserted by invoice date order and adds the row numbers to the @invid variable:

INSERT INTO Invoices1(invid, dt)  SELECT @invid + ROW_NUMBER() OVER(ORDER BY dt), dt FROM inserted

For example, if the current sequence value is 2 and you insert three new invoices in one INSERT statement, the new invoices get the IDs 3 (2+1), 4 (2+2), 5 (2+3), according to invoice date order. If you don't care about the order in which you assign IDs to the new invoices, simply replace ORDER BY dt with ORDER BY NULL.

To alter the existing triggers, which support only single-row inserts, and change them to support multirow inserts, run the code that Listing 3 shows. To test the triggers, you can run Listing 4's multirow insert queries against Invoices1 and Invoices2. Now query both tables, and you'll see the new invoice IDs 5, 6, 7 the query generated in Invoices1 and invoice IDs 8, 9, 10 in Invoices2.

By the Numbers

SQL Server 2005 doesn't provide a built-in independent sequencing tool. However, by using the new ROW_NUMBER() function, you can develop a simple, efficient custom sequence of your own. The ROW_NUMBER() function is one of my favorite new features in SQL Server 2005 because it has so many interesting applications. With this function, I've been able to simplify and improve the performance of many solutions to problems I'd struggled with for a long time.