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)
)
GO


ALTER TABLE tblIdentityCheck DROP COLUMN SerialNo
GO

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

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')
GO
INSERT INTO tblIdentityCheck VALUES (10, 'Robo')
GO

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.


BEGIN TRAN


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


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


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

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: