Press "Enter" to skip to content

Reasons Why We Get Identity Column Gaps

Steve Jones walks through several reasons why you might see gaps in identity columns:

Deleting Rows
This is noted in the tweet as a cause, but let’s test this.
One of the common ways that we get gaps in identity values is when rows are deleted. Let’s remove the row with Steve in it.

Steve explains a few others, but even that’s not complete: identity columns can jump after the service restarts as well.

In short, please do not use identity values in cases where you need to guarantee sequentiality (like check numbers or invoice numbers). Don’t use sequences either, as they’ll behave similarly.