Thinking outside the box

Patron Saint of Lost Yaks
posts - 203, comments - 734, trackbacks - 4

My Links

Advertisement

News

Archives

Post Categories

An alternative to IDENTITY column

Lately I have seen a number of questions regarding incremental update of some sort of counter placed in a central table.
The original posters will for some reason not use an IDENTITY column which has a minimum overhead and use of system resources.
And today I saw this type of question again (http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=114970) and I started to think if there actually were an alternative to IDENTITY column for their cases. I found a solution. I can't tell if it's elegant or not, but it work 100%.
If you are using SQL Server 2005 or later, you can stop reading here because Michael Valentine Jones has a working solution using the OUTPUT operator. But if you are using SQL Server 2000 or want to see a similar approach, please keep reading.

This is how you test my solution.
Open two new query windows in either Query Analyzer or Management Studio.

In first query window, copy and paste this code below

CREATE TABLE tSample
              (
                     i INT NOT NULL
              )

INSERT tSample values (0)

CREATE TABLE tLog
              (
                     i INT NOT NULL,
                     src VARCHAR(30) NOT NULL
              )

DECLARE       @i INT

WHILE 1 = 1
       BEGIN
              UPDATE tSample
              SET    @i = i = i + 1

              INSERT tLog
              SELECT @i - 1, 'Taken by code window 1'
       END

SELECT *
FROM   tLog

DROP TABLE    tLog,
              tSample

In the second query window, copy and paste this code below

DECLARE       @i INT
 
UPDATE tSample
SET    @i = i = i + 1

INSERT tLog
SELECT @i - 1, 'Taken by code window 2'

Now change to first query window and execute all the code. There is an never-ending loop (until you reach 2+ billion iterations anyway) so now you change to second code window and run that code while code in first query window is still executing.
You can run the code in second query window several times if you want to.

When you're done with second code window, change back to first code window and stop execution. Highlight last SELECT statement and DROP statement and execute to see what's in the tLog table.

As you can see, there are no duplicate values and no dead-locking. Everybody's happy!

Print | posted on Friday, November 28, 2008 8:48 AM | Filed Under [ Optimization SQL Server 2008 Algorithms Administration SQL Server 2005 SQL Server 2000 ]

Feedback

Gravatar

# re: An alternative to IDENTITY column

Just because you can do something, does not mean that you should do it. We can design totally denormalized tables also, but we should never do it except as part of a data mart/warehouse solution.
12/2/2008 5:20 PM | Raibeart
Gravatar

# re: An alternative to IDENTITY column

I can think of scenarios where this can be handy.
The cases are when you absolutely need all primary keys to be unique across whole database, and not just within a table.
12/2/2008 5:25 PM | Peso
Gravatar

# re: An alternative to IDENTITY column

So is there a link to Mr. Jones' OUTPUT script?
12/2/2008 6:26 PM | Seth
Gravatar

# re: An alternative to IDENTITY column

I dislike maintaining a log table for something so simple. Over time, the log table can get deleted, corrupted, or somehow get out of sync with the data table unless you also have very strict update policies and procedures. A trigger that determines the greatest current value, increments it, then inserts the new value on record creation is a much stronger solution.
12/2/2008 7:57 PM | Ron Kunce
Gravatar

# re: An alternative to IDENTITY column

Yes, the link is in the third sentence.
12/2/2008 9:00 PM | Peso
Gravatar

# re: An alternative to IDENTITY column

Yes. A trigger is one approach with both benefits and drawbacks.
One mayor drawback is concurrency and which function of @@IDENTITY, SCOPY_IDENTTY() and IDENT_CURRENT() to use.
12/2/2008 9:04 PM | Peso
Gravatar

# re: An alternative to IDENTITY column

This can be handy if you are ignoring identity column for one or two or three table but we should not use it for a whole db. because otherwise one needs to create lot of tables to handle only identity column for relative table. Don't you think?
12/6/2008 6:03 AM | Rajkumar Rajput
Gravatar

# re: An alternative to IDENTITY column

If you are looking for a real "Outside the box" solution for this type of problem, you would probably be better off installing a free express copy of Oracle, creating a sequence and retrieve the latest via a link server. It is always unique and you shouldn't see any blocking either.
12/8/2008 7:26 PM | Tim Nelson
Gravatar

# re: An alternative to IDENTITY column

I'll give you credit for your suggestion!
12/8/2008 9:47 PM | Peso
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET