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.
