One of my developer friend called me up today to ask if we can modify an existing column to make it IDENTITY column. I told him that we cannot do it through SQL Command like ALTER TABLE command but can do from SSMS design mode. Although this is not a good idea to make this change if the existing table has millions of rows (I will tell you the reason little later ).
Here, I will show the steps to do add Identity property on an Existing Column. Lets first create a simple table for the demonstration purpose:
CREATE TABLE identity_test
cr_date DATETIME DEFAULT GETDATE()
Lets do a SELECT from the above table:
On object explorer–> Right click on the above table and select “Design”
Look at the Identity_Specification column properties for column id ( on which we want to add the Identity Property).
We need to change the value to Yes:
Now lets generate the script to see what it is doing under the hood:
/* To prevent any potential data loss issues, you should review this script in detail before running it outside the context of the database designer.*/
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
ALTER TABLE dbo.identity_test
DROP CONSTRAINT DF__identity___cr_da__367C1819
CREATE TABLE dbo.Tmp_identity_test
id int NOT NULL IDENTITY (1, 1),
cr_date datetime NULL
) ON [PRIMARY]
ALTER TABLE dbo.Tmp_identity_test SET (LOCK_ESCALATION = TABLE)
ALTER TABLE dbo.Tmp_identity_test ADD CONSTRAINT
DF__identity___cr_da__367C1819 DEFAULT (getdate()) FOR cr_date
SET IDENTITY_INSERT dbo.Tmp_identity_test ON
IF EXISTS(SELECT * FROM dbo.identity_test)
EXEC(‘INSERT INTO dbo.Tmp_identity_test (id, cr_date)
SELECT id, cr_date FROM dbo.identity_test WITH (HOLDLOCK TABLOCKX)’)
SET IDENTITY_INSERT dbo.Tmp_identity_test OFF
DROP TABLE dbo.identity_test
EXECUTE sp_rename N’dbo.Tmp_identity_test’, N’identity_test’, ‘OBJECT’
Basically it is dropping all the constraints from our present table then it is creating a temporary table with similar structure as of our table and creating all the constraints and then it is inserting all the records from our present table to the newly created temporary table and then dropping our existing table and renaming the temporary table.
This is the reason I said in the beginning it is not a good idea to do this if the current table has millions of records because copying those millions of records from one table to another is a highly resource intensive and time-consuming activity.
Let me know if there is any other way to do the same activity.