ADD IDENTITY TO COLUMN

You have an existing table (in my case Products) and you need to turn an existing int column into an IDENTITY column. Here’s the problem: SQL Server has no ALTER COLUMN ... IDENTITY syntax — identity is a property of the column definition that can only be set at creation time. Even SSIS won’t do the conversion for you, and the SSMS table designer handles it by rebuilding the table behind the scenes. The rebuild below is the standard workaround, and it hasn’t changed since SQL Server 2005.

The workaround: rebuild the table

-- Make sure the column is a suitable integer type first
ALTER TABLE [dbo].[Products]
ALTER COLUMN [Code] int NOT NULL;
GO

-- Rename the table to a temp table to hold the data
-- (any dependencies on the original table will be lost)
EXEC sp_rename 'Products', 'ProductsTemp';
GO

-- Recreate the table with IDENTITY
CREATE TABLE [dbo].[Products] (
    [Code]  INT IDENTITY PRIMARY KEY,
    [Descr] nvarchar(250),
    [Price] money
);
GO

-- Allow explicit values into the identity column
SET IDENTITY_INSERT [dbo].[Products] ON;
GO

-- Copy the data across
INSERT INTO [dbo].[Products] ([Code], [Descr], [Price])
SELECT [Code], [Descr], [Price]
FROM [dbo].[ProductsTemp];
GO

SET IDENTITY_INSERT [dbo].[Products] OFF;
GO

-- Drop the old table
DROP TABLE [dbo].[ProductsTemp];
GO

-- Recheck the identity seed
DBCC CHECKIDENT ('dbo.Products', RESEED);
GO

Details that matter

Wrapping it in a transaction is wise. A failure between sp_rename and the final reseed leaves you with two half-states. BEGIN TRAN … COMMIT around the whole sequence, and test it on a copy of the table first.

IDENTITY_INSERT is session-wide: only one table per session can have it ON at a time, which is a classic source of “table is not user operable” errors in batch scripts.

The reseed is easy to get wrong. After inserting explicit values, run DBCC CHECKIDENT ('dbo.Products', RESEED) and verify the current identity value, or set it explicitly: DBCC CHECKIDENT ('dbo.Products', RESEED, @MaxCode). Note that after a reseed to N, the next inserted row gets N+1.

Alternatives worth knowing: if you just need generated numbers rather than a true identity column, SQL Server 2012+ has SEQUENCE objects, which are independent of any column and much easier to retrofit. Adding a new identity column, copying data, dropping the old column, and renaming also avoids the rename step — at the cost of the column’s ordinal position.

The same approach still works on every SQL Server version current today, including on Azure SQL Database — the syntax involved hasn’t changed, and Microsoft still hasn’t added a direct ALTER COLUMN ... IDENTITY.