A colleague asked me this morning if when you rolled back a transaction the identity values got reset to where they were before you started. I wasn't sure, I suspected not, but figured I'd try it and find out...

I have a simple table called test, which has 2 columns
[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 it

INSERT INTO [test] ([word])
VALUES ('Insert before transaction')

BEGIN TRAN

INSERT INTO [test] ([word])
VALUES ('1st insert inside transaction')

INSERT INTO [test] ([word])
VALUES ('2nd insert inside transaction')

ROLLBACK

INSERT INTO [test] ([word])
VALUES ('Insert outside of transaction')

When I then get the data back out, I have the following items
Id   Word
1   Insert before transaction
4   Insert outside of transaction

which 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.