Do identity columns get reset after a rollback transaction?
[Id] [int] IDENTITY(1,1) NOT NULL
[Word] [nvarchar](50) NULL
into which I inserted 4 different values as follows, 2 outside of a (rolled back) transaction and 2 within itINSERT INTO [test] ([word])
VALUES ('Insert before transaction')BEGIN TRANINSERT INTO [test] ([word])
VALUES ('1st insert inside transaction')INSERT INTO [test] ([word])
VALUES ('2nd insert inside transaction')ROLLBACKINSERT INTO [test] ([word])
VALUES ('Insert outside of transaction')When I then get the data back out, I have the following items
1 Insert before transaction
4 Insert outside of transactionwhich shows that although the data gets rolled back, the identity value doesn't get reset back to what it was before the transaction. This can always be done by hand using DBCC CHECKIDENT if necessary.