Tags

, , , , ,


SQL Server internally manages the values of Identity Columns and does not generate any Duplicate Values. The problem occurs when we manually insert or RESEED the Identity Values.

Today I will give a couple of examples which can result in Duplicate Identity Values :

RESEED Identity Values can generate Duplicate Records:

Whenever we RESEED the identity values in any table then we should always be extra-cautious as this might lead to generation of Duplicate Values in the Identity Column and SQL Server does not give any errors if we do not have any Primary Key Constraint or Unique Index on the Identity Column. The below example should be more helpful:

— Creating a Table with Identity Column
CREATE TABLE Department
(
id INT IDENTITY (1,1),
Department_Name VARCHAR(50)
)

— Inserting Couple of Records with Default Identity Values
INSERT INTO Department (Department_Name ) VALUES (‘Technology’)
INSERT INTO Department (Department_Name ) VALUES (‘Finance’)

SELECT * FROM Department

Lets RESEED the value of the Identity column.

DBCC CHECKIDENT(Department,RESEED,1)

— Inserting another Record with Default Identity Values
INSERT INTO Department (Department_Name ) VALUES (‘HR’)

SELECT FROM Department

So we can see that we might get duplicate values in the Identity Column if we are not careful while RESEEDing the value of Identity Column.

Below is another case which can cause similar problem:

Inserting Explicit Values in the Identity Columns can result in Duplicate Values:

— Creating a Table with Identity Column
CREATE TABLE Department
(
id INT IDENTITY (1,1),
Department_Name VARCHAR(50)
)

— Inserting Couple of Records with Default Identity Values
INSERT INTO Department (Department_Name ) VALUES (‘Technology’)
INSERT INTO Department (Department_Name ) VALUES (‘Finance’)

SELECT * FROM Department

— Manually Inserting Identity Values in the Identity Column
SET IDENTITY_INSERT Department ON
INSERT INTO Department (Id,Department_Name ) VALUES (1,‘HR’)
SET IDENTITY_INSERT Department OFF

SELECT FROM Department

Hopefully now we fully understand that Identity Columns do not guarantee Unique Values ALWAYS.

Cheers,

Subhro Saha

Advertisements