Adding IDENTITY to an Existing Column With ALTER TABLE SWITCH

SQL Server cannot turn an existing populated column into IDENTITY with a simple ALTER COLUMN. The SSMS designer can do it by copying the table, which is expensive for large data. Adding IDENTITY to an existing column works through an empty target table and ALTER TABLE SWITCH. When the strict switch rules are met, the rows move as a metadata operation.

Why an Existing Column Cannot Just Gain IDENTITY

IDENTITY is a property of a column definition, not a sequence pasted onto stored values. The destination table must already define the column as IDENTITY. SWITCH transfers the allocation units into that empty destination without inserting every row. Existing key values remain the same. New inserts need the identity seed to be set above the largest existing value.

I treat this as a schema migration, not a trick to run blindly on a large production table. Which foreign keys, triggers, permissions, and application references point to the original object ID? A metadata-fast row move can still require a long dependency and cutover plan.

Build a Small Isolated Example

Use a lab database and two simple heaps with identical column types and nullability. The only intentional difference is IDENTITY on OrderID. Start the target empty. A real table's indexes, constraints, compression, and partitioning must be matched or reviewed before SWITCH can succeed.

DROP TABLE IF EXISTS dbo.SwitchSourceDemo;
DROP TABLE IF EXISTS dbo.SwitchTargetDemo;
CREATE TABLE dbo.SwitchSourceDemo
(
    OrderID int NOT NULL,
    Note nvarchar(50) NULL
);
CREATE TABLE dbo.SwitchTargetDemo
(
    OrderID int IDENTITY(1,1) NOT NULL,
    Note nvarchar(50) NULL
);
INSERT dbo.SwitchSourceDemo(OrderID, Note)
VALUES (10,N'first'),(20,N'second'),(30,N'third');
SELECT COUNT(*) AS source_rows FROM dbo.SwitchSourceDemo;
SELECT COUNT(*) AS target_rows FROM dbo.SwitchTargetDemo;

The values deliberately have gaps. IDENTITY does not fill them during SWITCH, and it does not guarantee gapless future keys. Confirm the target count is zero immediately before the operation. An application writing into either table during cutover can invalidate that assumption.

Switch the Rows as Metadata

Run ALTER TABLE SWITCH under an approved maintenance lock or outage. For this unpartitioned example, the source becomes empty and the target receives all three rows. Check counts in the same transaction before committing. The statement can fail if table structures or constraints are incompatible, and it needs schema modification locks even though it avoids copying row data.

BEGIN TRAN;
ALTER TABLE dbo.SwitchSourceDemo
SWITCH TO dbo.SwitchTargetDemo;
SELECT COUNT(*) AS source_rows FROM dbo.SwitchSourceDemo;
SELECT COUNT(*) AS target_rows FROM dbo.SwitchTargetDemo;
COMMIT;

In production, include error handling and a rollback plan. Do not wrap a complex deployment in one open transaction while waiting for hours on a lock; test lock acquisition and runtime. Metadata-only does not mean zero blocking. A larger table can still take time to validate constraints and wait for concurrent users.

Reseed IDENTITY Above the Existing Column Values

The target has an IDENTITY definition, but its internal current value needs to align with the data moved into it. Check the maximum OrderID, then run DBCC CHECKIDENT with RESEED and no value. That form sets the current value from the column's maximum, so the next insert receives the next increment. Test overflow when keys approach the int limit.

SELECT MAX(OrderID) AS max_order_id
FROM dbo.SwitchTargetDemo;
DBCC CHECKIDENT (N'dbo.SwitchTargetDemo', RESEED);
INSERT dbo.SwitchTargetDemo(Note) VALUES (N'new row');
SELECT OrderID, Note
FROM dbo.SwitchTargetDemo ORDER BY OrderID;

The new row gets 31 in this example. An explicit RESEED 30 is a trap here. No row was ever inserted into the target table, so SQL Server hands out the reseed value itself, and my test row got 30, a duplicate. Record the verified max in the change log. An identity property allocates values; uniqueness still requires a primary key or unique index where the business rule demands one.

Check Every SWITCH Requirement

Source and target need compatible columns in the same order, matching data types and nullability, appropriate indexes, aligned partitions or filegroups, compatible constraints, and an empty target. Foreign keys and indexed dependencies can block the operation or make the cutover more complex. A mismatch produces an error rather than a quiet copy. Rehearse on a restored production schema, not only the two-column demo.

Script indexes, check constraints, defaults, triggers, permissions, statistics, and dependent objects before building the target. Some object-level permissions are tied to the old object ID and do not follow rows into the target. If applications reference the original table name, plan a controlled rename or synonym cutover after SWITCH. That naming step is separate from the row move and can break dependencies if rushed.

Compare With a Copy Migration

SSMS designer can create a replacement table and copy every row, producing I/O, log, and long locks. SWITCH can avoid that data movement when requirements align. It is not always available: a table with incompatible constraints or partition layout can need a staged copy or a sequence/default approach instead. Choose based on the real schema and acceptable outage.

I measure rehearsal time for target creation, index scripting, lock wait, SWITCH, reseed, cutover, and validation. A fast SWITCH inside a long migration is still useful, but the full plan determines risk. Validate existing values, next inserted value, permissions, and application queries before closing the change.

Validate Existing Column Values Before IDENTITY Takes Over

Before the switch, check for duplicate or NULL values and compare them with the target's intended constraints. IDENTITY itself does not enforce uniqueness, but a primary key will. A target primary key can prevent SWITCH unless the source has a matching index and constraint structure. If existing keys are negative or have gaps, decide whether future values should continue above the maximum or follow a different sequence policy. The reseed must be based on that decision.

Treat Names and Object IDs Separately

After SWITCH, rows live under the target table's object ID and name. Dropping the empty source and renaming the target can restore the familiar name. Foreign keys, permissions, triggers, and schema-bound dependencies refer to objects in ways a rename alone does not repair. Script and test those relationships. An application deployment must be coordinated with the table cutover, especially if code assumes the old object remains available throughout.

Rehearse Lock and Rollback Behavior

ALTER TABLE SWITCH is metadata-based when eligible, yet it requires schema modification locks. A long reader can delay it, and the held lock can block new work while the migration waits. Use a test workload with representative concurrency, a sensible lock timeout, and a documented retry. If the switch fails, the source rows remain in their original table; verify that state before continuing.

I keep row counts, key min and max, sample checksums, and index states before and after. The post-migration insert verifies the property that motivated the change, not only that existing rows moved. A controlled cutover is successful when both old data and future key generation behave as intended.

Related reading on this blog: Identity Column is Difficult to Remove and Find Missing Identity Values.

SWITCH is not a finished identity migration, it is a fast row move followed by reseed and checks.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.