One of the useful features in SQL Server is the ability to use identity columns. This feature allows you to automatically generate unique values for each row in a table. While it is easy to add or drop an identity column, modifying an existing column to make it an identity column or removing the identity property from an existing column can be a bit more challenging.

Unfortunately, there is no straightforward way to turn on or turn off the identity feature for an existing column. The recommended approach is to create a new column with the desired identity property or create a new table and migrate the data. Let’s explore a few examples to understand this better.

Example 1: Modifying an Existing Column

Consider a simple table with two columns, where one column is an identity column:

CREATE TABLE [dbo].[Test1](
   [id] [int] IDENTITY(1,1) NOT NULL,
   [name] [nchar](10) NULL
)

If we use SQL Server Management Studio to remove the identity property from the “id” column, the following steps are performed:

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_Test1
   (
   id INT NOT NULL,
   name NCHAR(10) NULL
   )  ON [PRIMARY]
GO

IF EXISTS(SELECT * FROM dbo.Test1)
    EXEC('INSERT INTO dbo.Tmp_Test1 (id, name) 
          SELECT id, name FROM dbo.Test1 WITH (HOLDLOCK TABLOCKX)')
GO

DROP TABLE dbo.Test1
GO

EXECUTE sp_rename N'dbo.Tmp_Test1', N'Test1', 'OBJECT' 
GO

COMMIT

Example 2: Modifying a Column with Constraints

Let’s make the example a bit more complex by adding a primary key and creating a second table with a foreign key constraint referencing the first table:

CREATE TABLE [dbo].[Test1](
   [id] [int] IDENTITY(1,1) NOT NULL,
   [name] [nchar](10) NULL,
   CONSTRAINT [PK_Test1] PRIMARY KEY CLUSTERED 
   (
      [id] ASC
   )
)

CREATE TABLE [dbo].[Test2](
   [id] [int] NULL,
   [name2] [nchar](10) NULL
) ON [PRIMARY]

ALTER TABLE [dbo].[Test2] WITH CHECK ADD CONSTRAINT [FK_Test2_Test1] FOREIGN KEY([id])
REFERENCES [dbo].[Test1] ([id])

ALTER TABLE [dbo].[Test2] CHECK CONSTRAINT [FK_Test2_Test1]

If we use SQL Server Management Studio to remove the identity property from the “id” column in the “Test1” table, the following steps are performed:

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_Test1
   (
   id INT NOT NULL,
   name NCHAR(10) NULL
   )  ON [PRIMARY]
GO

IF EXISTS(SELECT * FROM dbo.Test1)
    EXEC('INSERT INTO dbo.Tmp_Test1 (id, name) 
          SELECT id, name FROM dbo.Test1 WITH (HOLDLOCK TABLOCKX)')
GO

ALTER TABLE dbo.Test2
   DROP CONSTRAINT FK_Test2_Test1
GO

DROP TABLE dbo.Test1
GO

EXECUTE sp_rename N'dbo.Tmp_Test1', N'Test1', 'OBJECT' 
GO

ALTER TABLE dbo.Test1 ADD CONSTRAINT
   PK_Test1 PRIMARY KEY CLUSTERED 
   (
   id
   ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO

COMMIT

BEGIN TRANSACTION
GO

ALTER TABLE dbo.Test2 ADD CONSTRAINT
   FK_Test2_Test1 FOREIGN KEY 
   (
   id
   ) REFERENCES dbo.Test1 
   (
   id
   ) ON UPDATE  NO ACTION 
    ON DELETE  NO ACTION 
    
GO

COMMIT

As you can see, modifying an existing column with constraints involves several steps, including creating temporary tables, moving data, dropping and renaming tables, and recreating constraints.

Other Approaches

Another approach to modifying identity columns is to add a new column with the desired identity property or add a new column without the identity property and migrate the data from the old column to the new column. After that, you can drop the old column and rename the new column using the sp_rename stored procedure. However, this approach can be time-consuming, especially if there are indexes, foreign keys, and other constraints on the columns.

It’s worth mentioning that there are some approaches found on the internet that involve modifying values in the system tables. While these approaches may work, they come with a risk of potentially damaging the data if not executed correctly. Therefore, it is crucial to thoroughly understand the implications before modifying system tables.

In conclusion, modifying identity columns in SQL Server can be a complex task. While there is no easy way to turn on or turn off the identity property for an existing column, the recommended approach is to create a new column or table and migrate the data. It is essential to carefully plan and execute these modifications, especially for large tables or busy databases.

Thank you for reading!

Article Last Updated: 2021-08-05