How to Add an Identity Property to an Existing SQL Server Column?

Adding Identity to an Existing SQL Server Column: Two Methods

Directly adding an identity property to an existing column in SQL Server isn't a simple task. This article outlines two methods to achieve this, each with its own trade-offs:

Method 1: Creating a New Table with Identity

This method preserves existing data.

<code class="language-sql">CREATE TABLE dbo.Tmp_Names (
  Id INT NOT NULL IDENTITY(1, 1),
  Name VARCHAR(50) NULL
) ON [PRIMARY]
GO

SET IDENTITY_INSERT dbo.Tmp_Names ON
GO

IF EXISTS (SELECT * FROM dbo.Names)
    INSERT INTO dbo.Tmp_Names (Id, Name)
    SELECT Id, Name
    FROM dbo.Names TABLOCKX
GO

SET IDENTITY_INSERT dbo.Tmp_Names OFF
GO

DROP TABLE dbo.Names
GO

EXEC sp_rename 'Tmp_Names', 'Names'</code>

Method 2: Adding a New Identity Column and Replacing the Old

This approach does not retain existing data in the new identity column.

<code class="language-sql">ALTER TABLE Names
ADD Id_new INT IDENTITY(1, 1)
GO

ALTER TABLE Names DROP COLUMN ID
GO

EXEC sp_rename 'Names.Id_new', 'ID', 'COLUMN'</code>

Important Considerations:

Remember to address any foreign key constraints linked to the original primary key column before implementing either method. Failure to do so will result in database errors.

For more detailed information and troubleshooting, consult this Microsoft SQL Server forum thread:

https://www.php.cn/link/16dd8c942ad630be7e5a12b681b3f5c4

The above is the detailed content of How to Add an Identity Property to an Existing SQL Server Column?. For more information, please follow other related articles on the PHP Chinese website!