Breaks in Identity sequence

[dragonwell]

What are the reasons why a table with an Int Identity column would be missing records of certain sequences (aside from someone actually deleting)? The obvious answer is if a record was inserted during a transaction but the transaction was rolled back. But is there some other condition that would cause it?

Thanks

[blue]_______________________________________[/blue]
Business Logic:"AND when tweetle beetles battle with paddles in a puddle, they call it a tweetle beetle puddle paddle battle AND..." - Dr. Suess

 

[SQLDenis]

That's about it
delete,rollback (explicit) or error (implicit rollback)

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[mrdenny]

Or the identity isn't setup to use every number (but that's rare).

If a table is replicated with transactional or merge replication then you can also have gaps.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[SQLDenis]

or someone uses SET IDENTITY INSERT ON
does a bunch of inserts with gaps

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/