#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.

Advertisements

One thought on “#0269 – SQL Server – IDENTITY columns – Myths – Duplicate Values cannot exist IDENTITY columns

  1. 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