... until the collector arrives ...

This "blog" is really just a scratchpad of mine. There is not much of general interest here. Most of the content is scribbled down "live" as I discover things I want to remember. I rarely go back to correct mistakes in older entries. You have been warned :)

2010-09-16

SQL Server vs IDENTITY ROLLBACK

SQL Server 2005 and 2008 support the concept of IDENTITY columns, which are integer columns that are automatically assigned a unique sequence number when rows are inserted into the table. Each table has a so-called IDENT_CURRENT value which remembers the last IDENTITY issued.

Curiously, IDENT_CURRENT values do not participate in transactions. Thus, once an identity value has been issued, it is issued permanently -- even if the containing transaction is rolled back. This is still true even if the transaction isolation level is serializable.

This makes one wonder how identity values are generated...

Here is some SQL that illustrates the problem.

CREATE TABLE zot (x INT IDENTITY, y INT)

GO

BEGIN TRANSACTION
INSERT INTO zot (y) VALUES (0)
SELECT 'first id: ', x FROM zot
ROLLBACK TRANSACTION

BEGIN TRANSACTION
INSERT INTO zot (y) VALUES (0)
SELECT 'second id: ', x FROM zot
ROLLBACK TRANSACTION

GO

DROP TABLE ZOT

The queries show that the first IDENTITY issued was 1 and the second was 2.

Blog Archive