Identity Column Rollback

David Alcock figures out how identity columns behave when transactions get rolled back:

Identity columns are a very commonly used feature within tables in SQL Server. Basically when specified as an identity a column will automatically increment by the specified value; so if we have an identity increment of 1 and insert 5 rows they will automatically be numbered 1 to 5.
One cautionary measure with identities is that they don’t reset themselves when rows are deleted. If we delete rows 4 and 5 the next row will still be populated as identity 6. That’s fine, but what happens if we rollback an insert.

Read on for the answer.

Related Posts

SQL Server Isolation Levels

Pamela M. takes us through a humorous journey covering transaction isolation levels in SQL Server: READ COMMITTED:  One step up (and the default for SQL Server).    A query in the current transaction can’t read data modified by someone else that hasn’t yet committed.  No dirty reads.  BUT….data could be changed by others between statements in […]

Read More

New(ish) VLF Status: 4

Paul Randal points out a new VLF status which can appear if you’re using an Availability Group: At least since I started working on the SQL Server team (just after 7.0 shipped) and since then there have only been two VLF status codes: 0 = the VLF is not active (i.e. it can be (re)activated […]

Read More

Categories

December 2016
MTWTFSS
« Nov Jan »
 1234
567891011
12131415161718
19202122232425
262728293031