Identity Gaps–#SQLNewBlogger

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

Many people think that that an identity property will ensure a consistent, increasing numerical value. I ran across this tweet that indicates that situation.

2018-12-21 12_13_51-Krista on Twitter_ _#SQLHelp Is there any other reason (other than a DELETE) for

This isn’t really true, for many reasons, but in this post I’ll look at the possible reasons we get gaps in identity values.

Normal Operation

Let’s start with a basic table that contains an identity value. I’ll use this code:

CREATE TABLE dbo.SalesOrderHeader
( OrderKey INT IDENTITY(1, 1)
, CustomerName VARCHAR(30)

Now I can insert a few rows. Note that the results shown below the code will contain increasing values for the OrderKey.

INSERT dbo.SalesOrderHeader (CustomerName) VALUES ('Andy')
INSERT dbo.SalesOrderHeader (CustomerName) VALUES ('Brian')
INSERT dbo.SalesOrderHeader (CustomerName) VALUES ('Steve')
INSERT dbo.SalesOrderHeader (CustomerName) VALUES ('Anna')

Each of these inserts is a separate transaction, and they cause the identity to increment.

2018-12-21 12_04_17-SQLQuery5.sql - Plato_SQL2017.sandbox (PLATO_Steve (59))_ - Microsoft SQL Server

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.

2018-12-21 12_06_46-SQLQuery5.sql - Plato_SQL2017.sandbox (PLATO_Steve (59))_ - Microsoft SQL Server

I clearly have a gap in OrderKey here now. What happens if we add a new row? The identity value is built for (some) efficiency and doesn’t fill the gap. Only the next value is kept. We insert a row and get a 5.

2018-12-21 12_08_00-SQLQuery5.sql - Plato_SQL2017.sandbox (PLATO_Steve (59))_ - Microsoft SQL Server

As a side note, there is no index on this table, and no ORDER BY clause, so you can clearly see that there isn’t a reason why I should expect the ORDERKEY column to be returned in numerical or even insert order.

The Rollback

One of the more common occurrences with inserts is a problem with the value. For example, in this table, I have allocated 30 characters. What happens if I run this code?

INSERT dbo.SalesOrderHeader (CustomerName) 
   VALUES ('A Really Long Name Van Something The Third')

I get an error, which is shown here.

Checking the table, I have no value:

2018-12-21 12_11_17-SQLQuery5.sql - Plato_SQL2017.sandbox (PLATO_Steve (59))_ - Microsoft SQL Server

Let’s insert a new value and see.

2018-12-21 12_12_00-SQLQuery5.sql - Plato_SQL2017.sandbox (PLATO_Steve (59))_ - Microsoft SQL Server

We get a gap. The value “6” was skipped because of the error. The identity was allocated, but the rollback of the transaction due to the error did not rollback the identity sequence.

Reseeding the Property

One of the other ways to miss a value is directly reseeding the table. I can use the DBCC CHECKIDENT function to accomplish this. In my case, let’s run this code and set the identity value to 20.


Now I can insert new values and I’ll get these results.

2018-12-21 12_17_28-SQLQuery5.sql - Plato_SQL2017.sandbox (PLATO_Steve (59))_ - Microsoft SQL Server

The identity value was set to 20 and the next insert will increment this and take 21, leaving a gap from 8 to 20.

Be Careful

Don’t depend on the identity property to give you uniqueness, consecutive values, or avoid duplicates. It is up to you to code properly to account for these values.


This post came about from helping someone understand the problems and limitations. I wrote this in about 20 minutes (with setup and testing) to ensure that I understood what I was explaining to someone.

You could write something similar to show that you know the ways in which identity works.

About way0utwest

Editor, SQLServerCentral
This entry was posted in Blog and tagged , , . Bookmark the permalink.

6 Responses to Identity Gaps–#SQLNewBlogger

  1. Pingback: Reasons Why We Get Identity Column Gaps – Curated SQL

  2. Greg says:

    The other common time I’ve seen this is when you failover a mirror or availability group (possibly other scenarios as well). Causes problems when your system uses the identity column as a natural key (e.g. invoice number) as your invoice numbers will suddenly jump by 1000’s which raises questions with auditors.


  3. way0utwest says:

    Yep, certainly a place where we lose some identity values.


  4. Zikato says:

    And how would you go about having no identity gaps? I remeber reading somewhere that it’s in Italy’s legislation that you can’t have gaps in certain data sequences. I thought perhaps sequence might do the trick, but transaction rollback and caching leave gaps as well.


  5. Doug says:

    Put an exclusive lock on the table, get the MAX(ID), increment it by 1, insert your data and if it succeeds, release the lock. 🙂


  6. Tony Palmeri says:

    NOTE: I commonly see gaps of 1000 numbers in an identity column. I found out that this can happen when you re-start the server. (I googled for it, I don’t remember exactly where I read it)


Comments are closed.