Remove the IDENTITY property from an existing column

In this post, I will discuss 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).

A while ago 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:

Common Method 1: Move the data to 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.

Basic Algorithm:

1. Create an identical new table (excluding the IDENTITY property) with a temporary name.
2. Copy/move all the data from the old table to the new one.
3. Rename the tables to replace the old with the new.

Problem:

The main problem with this method is that it’s a huge pain in the rear 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).

Common Method 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:

1. Add a new column with a temporary name, with identical properties as the identity column (besides the IDENTITY property of course).
2. Execute an UPDATE statement to set the value of the new column to the value of the identity column in each row.
3. Drop the identity column.
4. 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 even if your identity column was first in the columns list, it will be the last after the above algorithm is executed. Unfortunately, in SQL Server you can’t add a new column in the middle of the column list using the ALTER TABLE… ADD … command.

Most of my client’s stored procedures relied on the order of the columns. Regardless of best practices on this particular unrelated topic, it’s easy to see that this method was 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 wasn’t more commonly used. I tried to look on 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 was practically impossible to find these sites unless you knew exactly what to look for.

Algorithm:

1. 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”.
2. Execute the following command: ALTER TABLE OriginalTableWithIdentity SWITCH TO NewTableWithoutIdentity
3. Rename the tables to replace the old with the new (using sp_rename).

Restrictions:

This method only works in SQL Server 2005 and newer.
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 underlying 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.

How about adding the IDENTITY property?

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.

Conclusion

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 at www.madeiradata.com