Add or drop identity property for an existing SQL Server column
One nice feature of SQL Server that is used quite a bit is the use of identity columns. This function gives you a simple way of creating a unique value for every row in your table. Adding a new column and making it an identity column is an easy thing to do as well as dropping an existing column that is an identity column, but how can you modify an existing column to make it an identity column or remove the identity property from an existing column?
Not sure how much you have researched this one so far, but there is no easy way to do this. By design there is no simple way to turn on or turn off the identity feature for an existing column. The only clean way to do this is to create a new column and make it an identity column or create a new table and migrate your data.
Let's take a look at a few examples:
Here is a simple table that has two columns and one column is the identity column.
If we use SQL Server Management Studio to get rid of the identity value on column "id", a new temporary table is created, the data is moved to the temporary table, the old table is dropped and the new table is renamed. This can be seen in the script below.
To get this script use Management Studio to make the change and then right click in the designer and select "Generate Change Script".
If we make this example a little more complicated, by having a primary key and creating a second table with a foreign key constraint referencing back to the first table we can see that even more work needs to be done.
If we do the same thing and use Management Studio to get rid of the identity value on column "id" in table "test1" and script out the change, we can see that even more steps need to take place.
- First a temp table "Tmp_Test1" is created with the correct column attributes
- The data is moved to "Tmp_Test1" from "Test1"
- The FK constraint on "Test2" is dropped
- Table "Test1" is dropped
- Table "Tmp_Test1" is renamed to "Test1"
- The primary key is created on table "Test1"
- And lastly the FK constraint is recreated on table "Test2". That's a lot of steps.
The same holds true if we want to change an existing column and make one of the columns an identity column. This scenario is probably less likely, but there may be a need.
Another approach would be to add a new column and make it an identity column or add a new column without the identity property and migrate the data from the old column to the new column. After that you could drop the old column and then rename the column using the sp_rename stored procedure. If you have indexes, foreign keys and other constraints on these columns you would still need to drop these prior to making the changes, so this approach is not much faster.
As you can see there really is not any easy way to do this. There are some other approaches that you can find on the internet that modify values in the system tables. These approaches do work, but if you make a mistake you could totally mess up your data, so make sure you understand what is in store before modifying system tables.
I wish there was a simpler way of turning on and turning off the identity property. For small tables or for databases that are not that busy this approach works without much issue. But if you have large tables or your databases are very busy it is kind of hard to drop constraints and tables on the fly like this. I guess we will have to wait and see if there are any changes in SQL 2008.
- Now that we have seen there is no easy way to do this, keep this in mind when designing your tables. Identity columns are great, but if you do need to change one it could be more painful then you think
- Try to avoid using identity columns as your primary key and for foreign key constraints just for this reason. I know once you start using them it is difficult to stop using them, but be aware of potential issues you may face down the line.
Last Updated: 2008-01-04
About the author
View all my tips