Should I Use IDENTITY or Not?

At first glance, it seems there's nothing special to consider before you use the IDENTITY column property, SQL Server's auto-numbering mechanism. But especially in large environments, you'll first want to understand some of IDENTITY's workings and limitations. In some cases, you might prefer to use your own auto-numbering or sequencing mechanism.To help you decide on the best course, I provide some facts about IDENTITY, discuss some of its limitations, and show two alternatives.

IDENTITY Basics

Here are a few things that you should know about the IDENTITY property:

Creating Custom Sequencing

Because of the IDENTITY limitations I've described, you might prefer to use your own sequencing mechanism. Let's look at alternatives for two common situations.

When you want the change in the sequence to be rolled back when a failure occurs or a transaction is rolled back, you need a mechanism that will lock the sequence, causing requests for subsequent sequence values to wait in a queue. In this case, for each sequence that you need to maintain, you can simply create a table with one row and one column, like this:

CREATE TABLE  Seq(val INT NOT NULL); INSERT INTO Seq VALUES(0); 

When you need a new sequence value, run the following specialized update, which increments and then retrieves the sequence value:

DECLARE @i AS INT; UPDATE Seq SET @i = val = val +1;SELECT @i; 

Every time you run this update, SQL Server places an exclusive lock on the sequence, increments the value, stores it in the @i variable, and then releases the lock. You can then use the @i variable for any purpose. Note that if this code appears in an external transaction, SQL Server releases the lock only when the whole transaction is complete. Thus, if the transaction fails, the sequence won't be incremented and no gaps or unaccounted-for numbers will be generated.

You can encapsulate this logic in a stored procedure that returns the new sequence value as an output parameter:

CREATE PROC usp_newseq @v  AS INT OUTPUT   AS UPDATE Seq    SET @v = val = val + 1; 

Whenever you need a new sequence value, call the procedure, like this:

DECLARE @i AS INT; EXEC usp_newseq @v = @i OUTPUT; SELECT @i; 

If you want an independent sequencing mechanism and you don't care about gaps in the sequence numbers, there's an even more efficient approach than the one I've just described. If gaps aren't a problem, there's no reason to lock the sequence until the end of the transaction. You can release it as soon as the value is incremented and you retrieve the sequence value. However, there's no way to tell SQL Server within an existing transaction that you want a certain part of the transaction to be committed, releasing the lock. SQL Server holds an exclusive lock until the end of the transaction, no matter what you do. But remember what I mentioned earlier about how the IDENTITY property increments regardless of whether a transaction succeeds or fails. There's a reason for this behavior. SQL Server locks the IDENTITY resource for a table only for the time required to increment the resource.

With this in mind, you can create a custom sequence based on the IDENTITY property. Create a table to maintain the sequence values, but this time with a column that has an IDENTITY property and no rows at all:

CREATE TABLE Seq2  (val INT NOT NULL IDENTITY); 

Every time you need a new sequence, use the code in Listing 1, which inserts a new row in the sequence table and retrieves the new IDENTITY value generated using the SCOPE_IDENTITY function. But there's a problem: Because all you do is insert a new row and then retrieve the new value, the sequence table keeps growing and you must clear the table once in a while. You can't clear it using the fast TRUNCATE TABLE statement because TRUNCATE TABLE resets the IDENTITY value to the seed. You can use a DELETE statement to clear the table, but as I mentioned earlier, DELETE can be slow because it's fully logged.

Here's a trick: Use a transaction with a savepoint to roll back the INSERT without affecting any external transaction in which this code might appear. You'll continue to get new sequence values, but the sequence table will never get a commit row and therefore won't continue to grow. You might want to encapsulate this code in a stored procedure like the one in Listing 2.

When you need a new sequence value, use this code:

DECLARE @i AS INT; EXEC usp_newseq2 @v = @i OUTPUT; SELECT @i; 

Ironically, you've used IDENTITY to avoid using IDENTITY. But of course, you've used IDENTITY in the sequence table to avoid using it in your base data tables, and now you have an independent sequencing mechanism.

Choosing IDENTITY or a Custom Sequence

Now that you've seen some of the limitations of using the IDENTITY property and also some alternatives, you can choose the best course for your situation. There are times when using the IDENTITY property makes sense. For example, you can let SQL Server generate key values in your table when the key column has no special meaning other than uniquely identifying the row. Before doing this, though, make sure you understand the limitations I've discussed here. For example, using IDENTITY to generate invoice numbers is a bad choice because failed INSERT statements generate gaps in the identity sequence.

When you need an independent sequencing mechanism (e.g., invoice numbers that you maintain in multiple tables), you'll have to develop your own. I've shown you two techniques, one that prevents gaps but is less scalable, and one that allows gaps and is more scalable.

A Note About Puzzles

A few months ago, when I first introduced logical puzzles to my column, I intended to provide a few puzzles, then stop. I wanted to show how logical SQL and T-SQL are and encourage you to practice logical puzzles. Besides being fun, puzzles improve your T-SQL problem-solving skills.

The feedback I've received about logical puzzles has been amazing, and I thank everyone who's contacted me. At times, the logical puzzles have spurred more interest than the T-SQL content, so I've decided to continue the puzzles. This month I'm introducing a new one (actually it's an old puzzle, but a new one for me) that I got from my father. You can find it in the sidebar "The Logical Puzzle." Enjoy!