Removing the IDENTITY Property from an Existing Column

Enterprise Manager drops the IDENTITY property from an existing column in much the same way that it adds the IDENTITY property, but you might want to consider alternatives to using Enterprise Manager if you never update the OrderID column. For example, when you want to insert your own order IDs rather than setting the IDENTITY column property to generate the order IDs automatically, you can turn on the IDENTITY_INSERT session option on the Orders2 table from the previous Q&A before you insert data. Note that a session option has two main features. The option affects only the session in which you issued the statement (in this case SET IDENTITY_INSERT ON). The option lets you insert your own values into the column but not update it.

Related: Adding the IDENTITY Property to an Existing Column

To allow column updates, you have to drop the column's IDENTITY property. If you remove the property through Enterprise Manager, the code that Enterprise Manager generates creates a new table, copies all the data from the original table to the new table, then renames the new table with the original table's name. However, if you prefer a shorter procedure that doesn't involve copying all the original data to another table and dropping the original table, you can write your own shortcut script. The shortcut script that Listing 3 shows adds a single column, updates its data to the IDENTITY column values, then drops the IDENTITY column.

The procedure in Listing 3 produces one gotcha: Regardless of the original IDENTITY column's ordinal position in the table, the new column appears as the table's last column. If the original IDENTITY column was the table's last column, you'll encounter no problems. However, if the original IDENTITY column was in any other position, your code might break when you use SELECT * to retrieve your table's data. This example is further proof that specifying column names in your production code when you retrieve data—even if you want to retrieve all of the table's columns—is good practice.

See also: Deleting an IDENTITY Column