SQL SERVER – Identity Column is Difficult to Remove

Let me state it first, if you already have an identity column on the table, there is no easy way to remove it. I discuss this during  Comprehensive Database Performance Health Check. It is true that the identity Column is Difficult to Remove.

SQL SERVER - Identity Column is Difficult to Remove IDcol-800x397

There is no easy way to remove the identity column from your table. You have two options in this scenario.

  1. Recreate the entire table with the column which is not an identity column
  2. Recreate the identity column with a different name and populate value from the identity column. Once done rename the column and drop the original identity column.

In both cases, there can be many issues when the identity key is used as a reference keys. I mean if the key is used as a foreign key or primary key. Indeed it is true that Identity Column is Difficult to Remove.

There are few other blog posts I have written about the identity column and I believe you may find them useful. Here is the video – Negative Identity Column – SQL in Sixty Seconds #101

Well, that’s it for today. If you liked this video, please do not forget to subscribe to my YouTube Channel – SQL in Sixty Seconds.

Here are my few recent videos and I would like to know what is your feedback about them.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

, ,
Previous Post
SQL SERVER – Unlocking User Without Changing Password
Next Post
MS Access – Count Distinct Values

Related Posts

1 Comment. Leave new

  • Imran Mohammed
    June 16, 2021 9:14 pm

    Microsoft made developer’s lazy with auto increment seed (Identity Columns). But it comes with some disadvantages. Sequences are good alternatives to Identity columns but does require additional efforts.

    We are so used to Identity Columns, especially in DW world, its impossible to picture a traditional DW without Identity columns for surrogate keys. But I now see many developers moving away from the choice of Identity columns for surrogate keys.

    Reply

Leave a Reply

Menu