Articles

You cannot add IDENTITY to an existing column in SQL Server

20 September 2026

Someone asks this most weeks. A table went to production with a plain int primary key, the application has been supplying the values, and now it needs to be an identity column. The obvious statement is:

ALTER TABLE dbo.Invoice ALTER COLUMN InvoiceId int IDENTITY(1,1) NOT NULL;

That is a syntax error. ALTER COLUMN has no identity clause, and there is no separate statement that adds one. The same is true in reverse: you cannot drop an identity, and you cannot change its seed or increment.

The version that is worse than an error

This one succeeds, which is the problem:

ALTER TABLE dbo.Invoice ALTER COLUMN InvoiceId int NOT NULL;

It runs, it reports success, and the identity property is exactly as it was. If the column had no identity, it still has none. If it had IDENTITY(1,1) and you meant to change it to IDENTITY(1000,1), it is still IDENTITY(1,1).

A deployment script full of statements like that will apply cleanly against every environment and leave the schema unchanged. It is worth knowing that a green deployment is not evidence here.

Option 1: rebuild the table

The dependable answer, and the one to reach for when the table is small enough that you can hold a lock on it for as long as the copy takes.

BEGIN TRANSACTION;

-- The new shape, with the identity you actually want.
CREATE TABLE dbo.Invoice_new
(
    InvoiceId   int IDENTITY(1,1) NOT NULL,
    CustomerId  int               NOT NULL,
    IssuedOn    date              NOT NULL,
    Total       decimal(19,4)     NOT NULL,
    CONSTRAINT PK_Invoice_new PRIMARY KEY CLUSTERED (InvoiceId)
);

-- Existing keys have to survive, so values are copied rather than regenerated.
SET IDENTITY_INSERT dbo.Invoice_new ON;

INSERT INTO dbo.Invoice_new (InvoiceId, CustomerId, IssuedOn, Total)
SELECT InvoiceId, CustomerId, IssuedOn, Total
FROM   dbo.Invoice WITH (TABLOCKX);

SET IDENTITY_INSERT dbo.Invoice_new OFF;

DROP TABLE dbo.Invoice;

EXEC sp_rename 'dbo.Invoice_new', 'Invoice';
EXEC sp_rename 'dbo.PK_Invoice_new', 'PK_Invoice', 'OBJECT';

COMMIT TRANSACTION;

Three things are easy to lose in that swap, and none of them announce themselves:

• Foreign keys pointing at this table. DROP TABLE will refuse while they exist, so they have to be dropped first and recreated afterwards — against the new table, with the same names, or your next schema comparison reports a difference that is only a rename.
• Indexes, defaults, check constraints and triggers. They belong to the old table and die with it. Script them before you start.
• Permissions. Grants are on the object, not the name.

Afterwards, check where the identity will carry on from:

DBCC CHECKIDENT ('dbo.Invoice', NORESEED);

If it is behind the highest value in the column, the next insert collides with an existing key:

DBCC CHECKIDENT ('dbo.Invoice', RESEED, 100000);

Option 2: switch the data in, without moving it

On a large table, copying every row is the expensive part — and it can be avoided. ALTER TABLE … SWITCH moves a table's data to another table as a metadata operation, and the two tables are allowed to differ in exactly the property we care about: one may have an identity where the other does not.

-- Same columns, same types, same nullability, same clustered index. Plus the identity.
CREATE TABLE dbo.Invoice_new ( /* … as above … */ );

ALTER TABLE dbo.Invoice SWITCH TO dbo.Invoice_new;

DROP TABLE dbo.Invoice;
EXEC sp_rename 'dbo.Invoice_new', 'Invoice';

The requirements are strict — the target must be empty, on the same filegroup, with matching columns and indexes — and SQL Server will tell you precisely which one you have missed. It is worth the trouble on a table of any size, because the switch itself is near-instant no matter how many rows are involved. Test it on a copy first: the failure messages are specific, but there are a lot of them.

Option 3: stop needing an identity

If what you actually want is "the database generates the next number", a sequence does that without touching the table's structure at all:

CREATE SEQUENCE dbo.InvoiceNumber AS int START WITH 100000 INCREMENT BY 1;

ALTER TABLE dbo.Invoice
    ADD CONSTRAINT DF_Invoice_InvoiceId DEFAULT (NEXT VALUE FOR dbo.InvoiceNumber) FOR InvoiceId;

That is an ordinary default constraint, added in place, with no rebuild and no downtime. The difference from an identity is that nothing stops an application supplying its own value — so it is a good answer for a new column or a table you control the writes to, and a poor one where the point was to make explicit values impossible.

A sequence can also be shared across tables, which an identity cannot, and read ahead of the insert with NEXT VALUE FOR when you need the key before the row exists.

Before any of this

Know what is pointing at the table. Foreign keys are the part that turns a fifteen-minute change into an afternoon, and they are the hardest to see from a table definition: the constraint lives on the other table, so reading the table you are about to rebuild does not show you what breaks.

That is the question WoodFireERD is built to answer — pick the table, and the diagram shows what points at it before you touch anything. It also refuses to pretend about this particular change: an identity change on an existing column comes out of the migration script as commented instructions rather than as a statement that would run and do nothing.