SQL SERVER – Identity Column is Difficult to Remove

The IDENTITY property is difficult to remove through a column alteration because SQL Server doesn’t support that operation. Existing values need protection.

1. Rebuild the table with an ordinary column and copy the existing values, preserving the complete table contract.
2. Where suitable, add an ordinary replacement column, populate it, reconstruct its dependencies, then deliberately retire and rename columns.

SELECT s.name AS SchemaName,t.name AS TableName,c.name AS ColumnName,
       c.seed_value,c.increment_value,c.last_value
FROM sys.identity_columns c JOIN sys.tables t ON t.object_id=c.object_id
JOIN sys.schemas s ON s.schema_id=t.schema_id ORDER BY s.name,t.name;

My original second option mistakenly recreated an identity column. The replacement must be non-identity. Review keys, indexes, defaults, triggers, permissions, replication and application references for either route.

Plan consistency with active writers and preserve a recoverable backup. Validate counts, keys and values on a copy. Dropping columns or swapping tables without dependency work can break consumers or lose metadata.

The inventory identifies identity columns without migrating them. The related articles and negative-identity video explain property and value behavior. Stored values and automatic generation are separate concerns.

Related reading

• Comprehensive Database Performance Health Check
• Negative Identity Column – SQL in Sixty Seconds #101
• SQL SERVER – Having Two Identity Columns for A Single Table
• SQL SERVER – Last Page Insert PAGELATCH_EX Contention Due to Identity Column
• SQL SERVER – Finding Out Identity Column Without Using Column Name
• SQL SERVER – Add Auto Incremental Identity Column to Table After Creating Table
• SQL SERVER – Jump in Identity Column After Restart
• SQL SERVER – Query to Find Seed Values, Increment Values and Current Identity Column Value of the Table with Max Value of Datatype – Part 2
• SQL in Sixty Seconds
• Optimize for Ad Hoc Workloads – SQL in Sixty Seconds #173
• Avoid Join Hints – SQL in Sixty Seconds #172
• One Query Many Plans – SQL in Sixty Seconds #171
• Best Value for Maximum Worker Threads – SQL in Sixty Seconds #170
• Copy Database – SQL in Sixty Seconds #169

An identity migration is not a simple rename, it is a data-and-dependency change that needs validation.

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