Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

My technology blog on SQL Server, TSQL, XML, FILESTREAM and other areas of SQL Server.
Browse by Tags · View All
XML 112
TSQL 69
XQuery 69
XQuery Functions 67
XQuery Training 65
XQuery in TSQL 64
XQuery Tutorial 63
SQL Server XQuery 63
XQuery-Labs 57
BRH 38

Archive · View All
September 2008 32
August 2008 30
July 2008 21
August 2009 19
June 2009 19
May 2010 18
January 2009 15
January 2010 14
October 2008 14
June 2008 13

SQL Server - My IDENTITY values are not sequential! Why do I have missing numbers in the IDENTITY column?

Jan 28 2009 11:59PM by Jacob Sebastian   

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.

Tags: IDENTITY, TSQL, SQL SERVER, SQL,


Jacob Sebastian
1 · 100% · 32225
1
 
0
Lifesaver
 
0
Refreshed
 
0
Learned
 
0
Incorrect



Submit

Your Comment


Sign Up or Login to post a comment.

"SQL Server - My IDENTITY values are not sequential! Why do I have missing numbers in the IDENTITY column?" rated 5 out of 5 by 1 readers
SQL Server - My IDENTITY values are not sequential! Why do I have missing numbers in the IDENTITY column? , 5.0 out of 5 based on 1 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]