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.
- 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.
The main problem with this method is that it’s a pain in the butt 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.
- 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.
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 even 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 only 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.
- 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”.
- Execute the following command:
ALTER TABLE OriginalTableWithIdentity SWITCH TO NewTableWithoutIdentity
- Rename the tables to replace the old with the new (using sp_rename).
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 commonly related to partitions (a feature which is available in Enterprise edition only, and in Standard edition starting with SQL Server 2016 SP1), the SWITCH TO command actually works in all editions of SQL Server! So even if you have Express edition it will work just fine.
You can also use this method to achieve the opposite goal (add the IDENTITY property to an existing column), but you must BEWARE when you do this: When you SWITCH TO into a table with an identity column, you must take into account that the IDENTITY value IS NOT UPDATED when you perform the switching. Therefore you may encounter duplicate identity values when inserting data into the table after the switch.
But you can easily update the identity seed value using the command:
DBCC CHECKIDENT ( table_name, RESEED, new_reseed_value )
And replacing new_reseed_value with the highest identity value of the data you switched.
Using this method, we’re able to remove (or add) the identity property instantly and efficiently, and without locking the table for more than several milliseconds.
If you have any comments, let me know!
This article was originally published by Eitan Blumin on February, 2011 in www.madeiradata.com