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!