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
- SQL Server first create a new table named dbo.Tmp_Items with an identity column and of course with same column names.
- Move the data from the Existing table (here table name is Items) to the new Tmp_Items table.
- Drop the Previous table (here table name is Items)
- Rename the Newly created table to the Old table name.
- At last create the indexes and Foreign-Key relationships.
- What we Observed ON Removing an IDENTITY From the Existing Table
- SQL Server first create a new table named dbo.Tmp_Items without an Identity column
- Move the data from the Existing table (here table name is Items) to the new Tmp_Items table.
- Drop the Previous table (here table name is Items)
- 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.