How to remove the IDENTITY property from an existing column using T-SQL (without changing the order of the columns or moving the data into a new table)
Recently I had a scenario at a client where I needed to remove the IDENTITY property of a column in an existing table. The two common methods to do this were quite problematic and were no good for us:
1. Move the data into a new table
This is the most common method, and it’s the one performed behind the scenes when you try to remove the IDENTITY property using SQL Server Management Studio.
Algorithm:
Create an identical new table (excluding the IDENTITY property) with a temporary name.
Copy/move all the data from the old table to the new one.
Rename the tables to replace the old with the new.
Problem:
The main problem with this method is that it’s a pain in the a** if your table is huge. It could take hours to move or copy the data between the tables, and it’s especially a problem if your table needs to be operational 24/7 (as was the case with my client).
2. Add a new column
This is the second most common method, and it’s the one most commonly found on the internet when looking for solutions to the problem of the previous method.
Algorithm:
Add a new column with a temporary name, with identical properties as the identity column (besides the IDENTITY property of course).
Execute an UPDATE statement to set the value of the new column to the value of the identity column in each row.
Drop the identity column.
Rename the new column to replace the original identity column.
Problem: The problem is that the only way to add a new column to a table without recreating the entire table is to the end of the column list. This means that if your identity column was first in the columns list, it will be the last after the above algorithm is executed. You can’t add a new column using ALTER TABLE… ADD … in the middle of the column list.
Most of my client’s stored procedures relied on the order of the columns. Then it’s easy to see that this method is also out of the question.
Then I came up with a workaround that worked extremely well.
The workaround: ALTER TABLE… SWITCH TO…
After coming up with this workaround, I was surprised that it’s not more commonly used. I tried to look in the internet to see if anyone else came up with this, and found around 2-3 sites and blogs that mention this method. Also, it’s practically impossible to find these sites unless you know exactly what to look for.
Algorithm:
a. Create a new table with a temporary name, identical to the original table (excluding the IDENTITY property). Let’s assume the name of our original table is “OriginalTableWithIdentity”, and the name of our new table is “NewTableWithoutIdentity”.
b. Execute the following command:
Transact-SQL
ALTER TABLE OriginalTableWithIdentity SWITCH TO NewTableWithoutIdentity
1
ALTER TABLE OriginalTableWithIdentity SWITCH TO NewTableWithoutIdentity
c. Rename the tables to replace the old with the new (using sp_rename).
Restrictions:
This method only works in SQL Server 2005 and newer. It won’t work in SQL 2000 and older.
The SWITCH TO command has several restrictions (most notably regarding foreign keys and indexes). Please visit here for more information about this command: http://msdn.microsoft.com/en-us/library/ms191160(v=SQL.90).aspx
What SWITCH TO does is simply changing the meta data of the partition underlining the table, by changing the ownership on the block of data to another table. There’s no data movement or modification involved at all.
Although this command is related to partitions (a feature which is available in Enterprise edition only), the SWITCH TO command actually works in all other editions of SQL Server as well. So even if you have Standard edition it will work just fine (I didn’t test it in other editions though, but I think it should work, so let me know if it doesn’t).
Using this method, I was able to remove the identity property instantly and efficiently, and without locking the table for more than several milliseconds.
If you have any comments, let me know!