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.
There is no easy way to remove the identity column from your table. You have two options in this scenario.
- Recreate the entire table with the column which is not an identity column
- 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
- SQL SERVER – Having Two Identity Columns for A Single Table
- SQL SERVER – Last Page Insert PAGELATCH_EX Contention Due to Identity Column
- SQL SERVER – Finding Out Identity Column Without Using Column Name
- SQL SERVER – Add Auto Incremental Identity Column to Table After Creating Table
- SQL SERVER – Jump in Identity Column After Restart
- SQL SERVER – Query to Find Seed Values, Increment Values and Current Identity Column Value of the Table with Max Value of Datatype – Part 2
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.
- Optimize for Ad Hoc Workloads – SQL in Sixty Seconds #173
- Avoid Join Hints – SQL in Sixty Seconds #172
- One Query Many Plans – SQL in Sixty Seconds #171
- Best Value for Maximum Worker Threads – SQL in Sixty Seconds #170
- Copy Database – SQL in Sixty Seconds #169
Reference: Pinal Dave (http://blog.SQLAuthority.com)
1 Comment. Leave new
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.