IDENTITY columns are not expected to be sequential. Most of the times you will notice that the IDENTITY columns have missing numbers. This behavior is by design, and there is nothing to worry about it. If your application requires a sequential number and cannot tolerate gaps in the sequence, you should make it a regular column and manage the values by yourself. An IDENTITY column cannot do that.

Let us examine a couple of cases that can cause missing numbers in the IDENTITY column.

Deletion of records can cause missing sequence numbers

One of the common reasons for IDENTITY values getting out of sequence (missing numbers) is deletion of records. Let us look at an example:

IF OBJECT_ID('Employees','U') IS NOT NULL
    DROP TABLE Employees
    
CREATE TABLE Employees (EmpID INT IDENTITY, Name VARCHAR(20))
GO

INSERT INTO Employees (Name) SELECT 'Jacob'
INSERT INTO Employees (Name) SELECT 'Steve'

SELECT * FROM Employees
/*
EmpID       Name
----------- --------------------
1           Jacob
2           Steve
*/

We created a table with an IDENTITY column and inserted two records into it. So far the EmpID column has values in sequence. Now let us delete the row with ID 2 and insert a new record.

DELETE FROM Employees WHERE EmpID = 2
INSERT INTO Employees (Name) SELECT 'Bob'
SELECT * FROM Employees
/*
EmpID       Name
----------- --------------------
1           Jacob
3           Bob
*/

Note that we have values out of sequence. EmpID 2 is missing. Let us now delete all the records and try to insert new records.

DELETE FROM Employees 
INSERT INTO Employees (Name) SELECT 'Jacob'
INSERT INTO Employees (Name) SELECT 'Steve'
SELECT * FROM Employees
/*
EmpID       Name
----------- --------------------
4           Jacob
5           Steve
*/

Note that after we deleted all records, the IDENTITY started from 4, not from 1.

Transaction rollbacks can cause missing sequence numbers

When you ROLLBACK a transaction, all IDENTITY values generated by the transaction will not rollback. This can cause missing numbers in the IDENTITY column. Let us look at an example to understand this.

IF OBJECT_ID('Employees','U') IS NOT NULL
    DROP TABLE Employees
    
CREATE TABLE Employees (EmpID INT IDENTITY, Name VARCHAR(20))
GO

INSERT INTO Employees (Name) SELECT 'Jacob'
INSERT INTO Employees (Name) SELECT 'Steve'

SELECT * FROM Employees
/*
EmpID       Name
----------- --------------------
1           Jacob
2           Steve
*/

We have values in sequence so far. Let us now see a TRANSACTION rollback can break the sequence.

-- insert a row and ROLLBACK
BEGIN TRAN
INSERT INTO Employees (Name) SELECT 'Bob'
ROLLBACK TRAN

-- insert another row and COMMIT
BEGIN TRAN
INSERT INTO Employees (Name) SELECT 'Bob'
COMMIT TRAN

SELECT * FROM Employees
/*
EmpID       Name
----------- --------------------
1           Jacob
2           Steve
4           Bob
*/

Note that your IDENTITY column has a missing value now. EmpID 3 was generated for the transaction that we rolled back. When the transaction rolls back, the IDENTITY values are not reset back to the previous value.

Question to Readers: I am eager to know how many of you think that IDENTITY columns missing sequence numbers are NOT acceptable.