Why are there gaps in my IDENTITY / AUTOINCREMENT column?

I have seen many people wonder why (or complain that) their IDENTITY or AUTONUMBER / AUTOINCREMENT column has gaps in the sequence. Before I explain situations which can cause gaps, I'd like to express that you really shouldn't care about gaps. IDENTITY values are a surrogate row identifier, and nothing more. They should not have any meaning external to the database and/or application. You should not identify customers, for example, by a CustomerID you generated artificially. So you should not rely on there being a CustomerID #35 just because there is a CustomerID #36. Another thing people do is use this number to determine how recently a row was inserted, or compare ... if you want to track this data, store the timestamp of when the row was added (see Article #2499 and Article #2448 for information). 
 
Having said that, here are some things that can cause gaps: 
 

---

DML statements are aborted 
 
For example, a CONSTRAINT violation will roll back the statement. 
 

CREATE TABLE splunge  (      splungeID INT IDENTITY(1,1),      splungeName VARCHAR(32) NOT NULL UNIQUE  )  GO    SET NOCOUNT ON    INSERT splunge(splungeName) SELECT 'foo'  INSERT splunge(splungeName) SELECT 'bar'  GO  INSERT splunge(splungeName) SELECT 'bar'  GO  INSERT splunge(splungeName) SELECT 'blat'  INSERT splunge(splungeName) SELECT 'munge'    SELECT * FROM splunge    DROP TABLE splunge  GO

 
Results: 
 

Server: Msg 2627, Level 14, State 2, Line 1  Violation of UNIQUE KEY constraint 'UQ__splunge__32E0915F'. Cannot insert duplicate key in object 'splunge'.  The statement has been terminated.    splungeID splungeName  --------- -----------  1         foo  2         bar  4         blat  5         munge

 

---

Manually rolling back a transaction 
 
Many people use error handling in their stored procedures, and if you roll back transactions that include insertion into a table with an IDENTITY column, the database removes the row from the table but doesn't reset the seed. (You could do this manually, if you really wanted to, but refer to the top of this article before deciding on that path.) 
 

CREATE TABLE splunge  (      splungeID INT IDENTITY(1,1),      splungeName VARCHAR(32) NOT NULL UNIQUE  )  GO    CREATE PROCEDURE dbo.insertSplunge      @splungeName VARCHAR(32)  AS  BEGIN      SET NOCOUNT ON            BEGIN TRAN          INSERT splunge(splungeName)              SELECT @splungeName            IF SCOPE_IDENTITY() = 2              ROLLBACK TRAN          ELSE              COMMIT TRAN  END  GO    EXEC dbo.insertSplunge 'foo'  EXEC dbo.insertSplunge 'bar'   EXEC dbo.insertSplunge 'blat'    SELECT * FROM splunge  GO    DROP PROC dbo.insertSplunge  DROP TABLE splunge  GO

 
Results: 
 

splungeID splungeName  --------- -----------  1         foo  3         blat

 

---

Deleting rows after the fact 
 

CREATE TABLE splunge  (      splungeID INT IDENTITY(1,1),      splungeName VARCHAR(32) NOT NULL UNIQUE  )  GO    SET NOCOUNT ON    INSERT splunge(splungeName) SELECT 'foo'  INSERT splunge(splungeName) SELECT 'bar'  INSERT splunge(splungeName) SELECT 'blat'  INSERT splunge(splungeName) SELECT 'munge'    DELETE splunge WHERE splungeName LIKE 'b%'  GO    SELECT * FROM splunge  GO    DROP TABLE splunge  GO

 
Results: 
 

splungeID splungeName  --------- -----------  1         foo  4         munge

 

---

Resetting the IDENTITY seed 
 
As Article #2237 explains, it is possible for you to change the seed of an IDENTITY column, even in cases where there is already data in the table. Here is a repro using DBCC CHECKIDENT: 
 

CREATE TABLE splunge  (      splungeID INT IDENTITY(1,1),      splungeName VARCHAR(32) NOT NULL UNIQUE  )  GO    SET NOCOUNT ON    INSERT splunge(splungeName) SELECT 'foo'  INSERT splunge(splungeName) SELECT 'bar'  GO    DBCC CHECKIDENT('splunge', RESEED, 100)      WITH NO_INFOMSGS  GO    INSERT splunge(splungeName) SELECT 'blat'  INSERT splunge(splungeName) SELECT 'munge'    SELECT * FROM splunge  GO    DROP TABLE splunge  GO

 
Results: 
 

splungeID splungeName  --------- -----------  1         foo  2         bar  101       blat  102       munge

 

---

If you know of any other scenarios that can cause gaps, please let us know. If you are looking for an easy way to FIND gaps in your IDENTITY column, please see Article #2516.