SideBar    The Logical Puzzle

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!

End of Article

Prev. page     1 [2]     next page -->



You must log on before posting a comment.

If you don't have a username & password, please register now.

Reader Comments

Another brilliant article (ideas) from Itzik. Such a simple solution to a complex problem, or at least, a problem most of us have spent time on, I just wish i'd thought of these solutions. I hate u Itzik -lol- :)

pmo511- December 13, 2005

Article Rating 5 out of 5

I'm not sure if this is the proper place to post this but I have a question. I was recently working on an SP that would do the same thing, but I used this syntax:

UPDATE [TABLE] SET @Val = [Column] + 1, [Column] = [Column] + 1

would this do the same thing as mentioned in the article:

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

parlay- December 29, 2005

Article Rating 5 out of 5

I would suggest a minor optimization. In many cases we know up front that more than one value is necessary. For instance if I am going to insert 15 new rows, I know in advance that I need 15 values. It would be nice to do it in just one stored procedure call. Let's modify the procedure a little bit:

CREATE PROC usp_newseq_interval @amount int, @v AS INT OUTPUT AS UPDATE Seq -- allocates @amount iunique values SET @v = val = val + @amount; go

and call it:

DECLARE @i AS INT; EXEC usp_newseq_interval 15, @v = @i OUTPUT; SELECT @i;

As a result of this call, 15 consecutive values have been allocated, and @i stores the last one of them.

alkuzo- February 10, 2006

Article Rating 5 out of 5

Smack! You are good.

Synenergy- August 24, 2008

Article Rating 5 out of 5

 
 

ADS BY GOOGLE