#0267 – SQL Server – IDENTITY columns – Myths – IDENTITY columns cannot have holes or “gaps”


Whenever a pattern evolves into a frequently used practice, it does not take long for myths to start getting associated with it. IDENTITY columns have become such a common pattern that often I hear about these myths from novice developers. Using this series, I hope to bust those myths once and for all.


Busting the Myth – IDENTITY columns cannot have holes


Whenever a break in a series is encountered, it is referred to as a hole. For example, if we have a series of numbers from 1 to 5 such that the number 2 is missing (1, 3, 4, 5), then we have a hole.


I often hear from developers that “Holes cannot be generated in the values of a column defined as the IDENTITY column” is a prime reason why one uses IDENTITY columns. This is in-fact incorrect as I will be proving by this post.


The Test


To provide that holes can be generated in an IDENTITY column, I will use the script shown below. The script:



  1. Prepares the environment by creating a table and adding some test data into it

  2. Check the current identity value of the table using IDENT_CURRENT()

  3. Opens a transaction

  4. Performs an insert into a table which has the IDENTITY property defined

  5. Later, the transaction is rolled back, and the current identity value of the table is queried using IDENT_CURRENT()

  6. To confirm, a record is inserted into the table and the values selected – clearly indicating the presence of the hole

USE tempdb;
GO

SET NOCOUNT ON;

--1. Prepare the environment
--   Create a table, and add some test data into it

--Safety Check
IF OBJECT_ID('tIdentity','U') IS NOT NULL
    DROP TABLE dbo.tIdentity;
GO

--Create a table
CREATE TABLE dbo.tIdentity (IdentityId INT IDENTITY(1,1),
                            IdentityValue VARCHAR(10)
                           );
GO

--Add some test data
INSERT INTO dbo.tIdentity (IdentityValue)
VALUES ('One'),
       ('Two'),
       ('Three');
GO

--2. Check the current identity value of the table using IDENT_CURRENT()
SELECT IDENT_CURRENT('dbo.tIdentity') AS IdentityValueBeforeTest;
GO

--3. Perform the test
--3a. Open a transaction
BEGIN TRANSACTION IdentityHolesCheck
    --3b. Perform an insert
    INSERT INTO dbo.tIdentity (IdentityValue)
    VALUES ('Four');
--3c. Rollback the transaction
ROLLBACK TRANSACTION IdentityHolesCheck;
GO

--4. Check the current identity value of the table using IDENT_CURRENT()
SELECT IDENT_CURRENT('dbo.tIdentity') AS IdentityValueAfterTest;
GO

--5. Confirmatory test
INSERT INTO dbo.tIdentity (IdentityValue)
VALUES ('Five');
GO

--6. Select the values from the table
SELECT ti.IdentityId, ti.IdentityValue
FROM dbo.tIdentity AS ti;
GO

--Cleanup
IF OBJECT_ID('tIdentity','U') IS NOT NULL
    DROP TABLE dbo.tIdentity;
GO

/**********************************************
               RESULTS
**********************************************/
/*
IdentityValueBeforeTest
-----------------------
3

IdentityValueAfterTest
----------------------
4

IdentityId  IdentityValue
----------- -------------
1           One
2           Two
3           Three
5           Five
*/

While this was a simulation, in a practical world this situation is similar to a failed insert. Even if the INSERT did not go through, the IDNETITY value was incremented leaving the hole behind.


Conclusion


The test shown above clearly proves that it is possible to have holes in an IDENTITY column. Any code that expects an uninterrupted series of values may break if this has not been taken into consideration.


Until we meet next time,


Be courteous. Drive responsibly.

Advertisements

4 thoughts on “#0267 – SQL Server – IDENTITY columns – Myths – IDENTITY columns cannot have holes or “gaps”

  1. manub22

    Nice post.

    There is one more myth with IDENTITY property that, it will return only unique values.
    But it is not so, a PK is also required to maintain unique values in a column.

    Small example, in addition to the above example:

    — reseed the identity to any existing value
    DBCC CHECKIDENT (tIdentity, reseed, 2)

    — check the new identity valie
    SELECT IDENT_CURRENT(‘dbo.tIdentity’) AS IdentityValueAfterTest;
    GO

    — this will insert duplicate identity values
    INSERT INTO dbo.tIdentity (IdentityValue)
    VALUES (‘ThreeDup’),
    (‘FourDup’),
    (‘FiveDup’);
    GO

    — check new records with duplicate identities:
    SELECT ti.IdentityId, ti.IdentityValue
    FROM dbo.tIdentity AS ti;
    GO

    /*
    IdentityId IdentityValue
    1 One
    2 Two
    3 Three
    5 Five
    3 ThreeDup
    4 FourDup
    5 FiveDup
    */

    Like

    Reply
  2. harsh

    There are many myths (mostly for new developers) regarding IDENTITY columns like, it can not have negative or zero value & it can not be decrement order for IDENTITY column.

    Like

    Reply
  3. Pingback: #0390 – SQL Server – SSIS – OLE DB Destination – Table Fast Load – Keep Identity option | SQLTwins by Nakul Vachhrajani

Let me know what you think about this post by leaving your feedback here!

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s