#0269 – SQL Server – IDENTITY columns – Myths – Duplicate Values cannot exist IDENTITY columns

The SQL Server database engine automatically generates the next value for use by the IDENTITY column on a table. This often leads to a misconception that IDENTITY columns always contain unique values.

The truth is that they don’t have to be unique. It is perfectly valid to have duplicate values in IDENTITY columns and today, I present two such scenarios under which this is possible:

Duplicate values in IDENTITY columns – Explicit Inserts

As already know, explicit values can be inserted into IDENTITY columns. It is therefore possible to insert duplicate values in this scenario:

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. Turn Explicit IDENTITY_INSERT ON and insert duplicate IDENTITY values
SET IDENTITY_INSERT dbo.tIdentity ON;
GO

INSERT INTO dbo.tIdentity (IdentityId, IdentityValue)
VALUES (2, 'Two2'),
       (3, 'Three2');
GO

SET IDENTITY_INSERT dbo.tIdentity OFF;
GO

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

SELECT * FROM dbo.tIdentity;
GO

--5. Run DBCC CHECKIDENT and see what happens
DBCC CHECKIDENT ('dbo.tIdentity');
GO
SELECT IDENT_CURRENT('dbo.tIdentity') AS IdentityValueAfterDBCC;
GO

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

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

IdentityValueAfterExplicitInsert
--------------------------------
3

IdentityId  IdentityValue
----------- -------------
1           One
2           Two
3           Three
2           Two2
3           Three2

Checking identity information: current identity value '3', current column value '3'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

IdentityValueAfterDBCC
----------------------
3
*/

As can be seen from the script above, we were successfully able to insert duplicate values for the IdentityId column in the table. Also, the current IDENTITY value continues to remain 3 although there are about 5 records inserted in the table.

Duplicate values in IDENTITY columns – Re-seed IDENTITY column

The DBCC CHECKIDENT can be used to get the current value of the IDENTITY column value. By default, this DBCC statement does not reseed the IDENTITY column. However, if the RESEED parameter is specified, it allows the administrator to reset the “seed” value of the column to any value supplied (Refer the Introductory Post for a refresher).

The query shown below:

Creates a test table and inserts some test data into it

Verifies the value of the IDENTITY column

Reseed the IDENTITY column using DBCC CHECKIDENT with the RESEED parameter

Insert some test data into the test table

Verify the values in the table and the current value of the IDENTITY column

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. Reseed the IDENTITY column
DBCC CHECKIDENT ('dbo.tIdentity', RESEED, 1);
GO

INSERT INTO dbo.tIdentity (IdentityValue)
VALUES ('Two2'),
       ('Three2');
GO

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

SELECT * FROM dbo.tIdentity;
GO

--5. Run DBCC CHECKIDENT and see what happens
DBCC CHECKIDENT ('dbo.tIdentity');
GO
SELECT IDENT_CURRENT('dbo.tIdentity') AS IdentityValueAfterDBCC;
GO

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

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

Checking identity information: current identity value '3', current column value '1'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

IdentityValuesAfterReseed
-------------------------
3

IdentityId  IdentityValue
----------- -------------
1           One
2           Two
3           Three
2           Two2
3           Three2

Checking identity information: current identity value '3', current column value '3'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

IdentityValueAfterDBCC
----------------------
3
*/

Duplicate values in IDENTITY columns – BCP/BULK INSERT/OPENROWSET – -E, KEEPIDENTITY, -f switches

A long time ago, I wrote about the BCP utility being an underappreciated feature of Microsoft SQL Server.

When data is exported or inserted in bulk via the bcp and BULK INSERT statements, it is possible to preserve the value of IDENTITY columns using the following switches:

BCP uses the –E switch

BULK INSERT uses the KEEPIDENTITY switch

OPENROWSET uses the –f switch

Conclusion

As is the case with all columns in SQL Server, uniqueness can only be guaranteed by the use of a UNIQUE constraint. Without the constraint, IDENTITY columns may end up having duplicate values.

Often, applications and T-SQL code use IDENTITY columns as a unique sequence of numbers – this should be done with due care as duplicate values may cause the code to break.

Until we meet next time,

Be courteous. Drive responsibly.