Adding OR Removing IDENTITY To and From the Existing Column Using T-SQL

Recently while I am designing a Database, I came across a situation to add an identity to an Existing column. Here I am using a table Items. First I thought, there will be an alter command to do this. I wrote a DD L statement like this ALTER TABLE ITEMS ALTER COLUMN IDENTITY(1,1) and it is throwing an Error like ‘incorrect syntax’. So I started to solve the problem and at last I found an amazing answer i.e.; we can’t directly ADD or REMOVE an IDENTITY column using T-SQL.

How I Find out?

First I want to find what is happening inside SQL Server when we are adding or removing an Identity to an existing column using SQL Server Management Studio. For that, I enabled the Auto generate change script option in SQL Server Management Studio.

  • Enabling change script in SQL Server Management Studio

Choose Tools ->Options -> select designers, in that choose

Auto  generate change scripts option from the right box.

  • Adding an  IDENTITY to  an  Existing Column Using SSMS:

Select the column which we want to enable the Identity and save      the   process.While saving a window appears showing the T-SQL statements.

T-SQL Statements behind the scene are,

–For Enabliling Identity Column

/* To prevent any potential data loss issues, you should review this script in detail before running it outside the context of the database designer.*/

–For Enabliling Identity Column

/* To prevent any potential data loss issues, you should review this script in detail before running it outside the context of the database designer.*/

BEGIN TRANSACTION

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

COMMIT

BEGIN TRANSACTION

GO

CREATE TABLE dbo.Tmp_Items

(

ItemID int NOT NULL IDENTITY (1, 1),

ItemType int NULL,

ItemName varchar(50) NULL,

GenericID int NULL

)  ON [PRIMARY]

GO

SET IDENTITY_INSERT dbo.Tmp_Items ON

GO

IF EXISTS(SELECT * FROM dbo.Items)

EXEC(‘INSERT INTO dbo.Tmp_Items (ItemID, ItemType, ItemName, GenericID)

SELECT ItemID, ItemType, ItemName, GenericID FROM dbo.Items WITH (HOLDLOCK TABLOCKX)’)

GO

SET IDENTITY_INSERT dbo.Tmp_Items OFF

GO

DROP TABLE dbo.Items

GO

EXECUTE sp_rename N’dbo.Tmp_Items’, N’Items’, ‘OBJECT’

GO

ALTER TABLE dbo.Items ADD CONSTRAINT

PK_Items PRIMARY KEY CLUSTERED

(

ItemID

) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO

COMMIT

  • Removing an IDENTITY Column Using SSMS

T-SQL Statements behind the scene are,

/* To prevent any potential data loss issues, you should review this script in detail before running it outside the context of the database designer.*/

BEGIN TRANSACTION

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

COMMIT

BEGIN TRANSACTION

GO

CREATE TABLE dbo.Tmp_Items

(

ItemID int NOT NULL,

ItemType int NULL,

ItemName varchar(50) NULL,

GenericID int NULL

)  ON [PRIMARY]

GO

IF EXISTS(SELECT * FROM dbo.Items)

EXEC(‘INSERT INTO dbo.Tmp_Items (ItemID, ItemType, ItemName, GenericID)

SELECT ItemID, ItemType, ItemName, GenericID FROM dbo.Items WITH (HOLDLOCK TABLOCKX)’)

GO

DROP TABLE dbo.Items

GO

EXECUTE sp_rename N’dbo.Tmp_Items’, N’Items’, ‘OBJECT’

GO

ALTER TABLE dbo.Items ADD CONSTRAINT

PK_Items PRIMARY KEY CLUSTERED

(

ItemID

) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO

COMMIT

  • What we Observed ON Adding an IDENTITY To the Existing Table
  1. SQL Server first create a new table named dbo.Tmp_Items with an identity column and of course with same column names.
  2. Move the data from the Existing table (here table name is Items) to the new Tmp_Items table.
  3. Drop the Previous table (here table name is Items)
  4. Rename the Newly created table to the Old table name.
  5. At last create the indexes and Foreign-Key relationships.
  • What we Observed ON Removing  an IDENTITY From the Existing Table
  1. SQL Server first create a new table named dbo.Tmp_Items without an Identity column
  2. Move the data from the Existing table (here table name is Items) to the new Tmp_Items table.
  3. Drop the Previous table (here table name is Items)
  4. Rename the Newly created table to the Old table name using sp_rename.At last create the indexes and Foreign-Key relationships.

Hope Everyone like this Article,Healthy comments are welcomed.