SQL Server tips and experiences dedicated to my twin daughters.

Generally, IDENTITY columns are used when developing new tables so that when records are inserted into a table, they are assigned the appropriate values.

Hence if ever the need arises to insert an IDENTITY column into an existing question, the big question is – can it be done?

Is it really possible to add an IDENTITY column to an existing table?

The answer is quite simple – Yes!

The only restrictions that ALTER TABLE enforces upon us are that a new column must be added and that it should be of a data-type compatible with IDENTITY Columns. To demonstrate these points, I will perform two experiments shown below.

Modifying an existing column to an IDENTITY column

Other than recreating the entire table, an existing column cannot be modified to an IDENTITY column. Copy-Paste the query below into SSMS and simply parse it by using Ctrl+ F5 key combination or the blue check mark on the SSMS toolbar. The following error will be encountered:

Tip: If your design ever needs to be modified to convert an existing column into an IDENTITY column, this can be achieved using the following high-level steps:

Create an identical table under a different name, but with an IDENTITY column (e.g. tmp_tIdentity)

Set IDENTITY_INSERT to ON for this new table

Pump data from the old table to the new table (i.e. from tIdentity to tmp_tIdentity)

Set IDENTITY_INSERT to OFF for this new table

Drop the old table

Rename the newly created table (i.e. rename from tmp_tIdentity to tIdentity)

Adding a new Identity column to an existing table

While existing columns cannot be converted to IDENTITY columns, new IDENTITY column can always be added to an existing table (provided there are no other IDENTITY columns on it).

The below script quite simply creates a table and then add an IDENTITY column to it. As can be seen from the results below, although the records were inserted randomly, the identity values are assigned based on the way in which the records are ordered. This is an important consideration, as we will see in part 02 of this post. In this case, we have a clustered key on the RecordId column and therefore, the values in the IDENTITY column follow the same order as the RecordId.

Conclusion (Part 01)

The above experiments show that Microsoft SQL Server allows us to define IDENTITY columns on existing tables, under certain restrictions. This can be a very useful feature in case an original, legacy design needs to be changed for a universal adoption of IDENTITY columns in your product.