Wednesday, September 21, 2011

How to add or modify IDENTITY property to an existing column in SQL Server?

Method 1: If there is no data in the table or column then use this method.

CREATE TABLE tblIdentityCheck
   SerialNo INT,
   FirstName VARCHAR(10)

ALTER TABLE tblIdentityCheck DROP COLUMN SerialNo

--By default, IDENTITY will start with 1 and increment by 1
Alter Table tblIdentityCheck Add SerialNo INT IDENTITY

Method 2: If there is data in the table then create a new table with IDENTITY column in it. Move the data from old table to new table.

1. Create a new table with identity column
2. Enable IDENTITY_INSERT for this new table
3. Move the data from this old table to this new table.
4. Disable IDENTITY_INSERT for this new table
5. Delete the old table.
6. Rename the newtable with the oldtable name.

Let me explain the above steps with an example. Assume the table already had these records in it.

INSERT INTO tblIdentityCheck VALUES (1, 'Vadivel')
INSERT INTO tblIdentityCheck VALUES (10, 'Robo')

Now once we make SerialNo as an Identity column it will automatically start incrementing by 1 from the max value already present in the table. In this case it will start from 11.


CREATE TABLE dbo.tblIdentityCheck_Staging
   SerialNo int NOT NULL IDENTITY (1, 1),
   FirstName varchar(10) NULL
SET IDENTITY_INSERT dbo.tblIdentityCheck_Staging ON

INSERT INTO dbo.tblIdentityCheck_Staging (SerialNo, FirstName)
SELECT SerialNo, FirstName FROM dbo.tblIdentityCheck WITH (HOLDLOCK TABLOCKX)

SET IDENTITY_INSERT dbo.tblIdentityCheck_Staging OFF
DROP TABLE dbo.tblIdentityCheck
EXEC sp_rename N'dbo.tblIdentityCheck_Staging', N'tblIdentityCheck', 'OBJECT'

Method 3: Easiest option of the lot :) Change the property in the SSMS

1. Goto SQL Server Management Studio
2. Right click on that table name
3. Choose Design/Modify
4. Choose the column which needs to be set as Identity
5. At the bottom window (Column Properties) for that column you can see "Identity Specification" has been by default set to "NO".
6. Just change it to "YES" and save the table (CTRL + S)

This is easier but internally SQL Server does exactly the same process what we have seen in Method 2.

No comments: