Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

I have read that after SQL Server 2000, the ability to "un-identity" an identity column was removed. And that this was "By Design" (not just a missing feature).

Here is an example I found on a blog. It involves updating the system tables. (And that ability was removed after SQL Server 2000.) I get that doing this via system tables is not a good idea. I am just wondering why a feature to do this another way is not around.

Working around this is going to cause me a considerable amount of work. (Copying many hundreds of millions of rows to new tables in a downtime intolerant environment.)

So I thought I would ask "Why".

What changed in Sql Server 2005 and later versions that made this a bad thing? Or was it always bad, and just not locked down?

What "Best Practice" (or similar principle) would be violated by making an identity column a normal column again?

--

Update to answer the request for "why I am doing this":
This is a very high level summary: I am going to start adding partitions to my tables. (So that I can archive/purge old data.) That is all easy. But I occasionally need to move a record to a different partition so it does not get removed (when a partition comes up for archival/deletion). (I am having my partitioning column increase by 2 so that there is always space to move the row to a different partition.)

But if the partitioning column is an identity column, then I have to delete and re-insert the value (there is no way to update the value of an identity column). Which causes issues with replication.

So I am wanting to use a sequence instead of an identity column. But that switch is very very hard on large databases.

1 Answer
1

Why can I no longer do this risky thing that I should never have been allowed to do in the first place?

The answer to that question is largely irrelevant (though you can see some Microsoft comments in these Connect items asking for this functionality: #294193 and #252226). For completeness, my synopsis is: The ability to remove the identity property was an unintended side effect of having the ability to mess with the system tables in the first place. This was not intended to be used in the many ways that it was, often with very bad consequences, and thus it was removed. It was an undocumented, unsupported, system table hack. The ability to change data in system tables wasn't removed because Microsoft no longer wanted you to hack your way out of a column being an identity column, it was removed because mucking with the system tables is extremely risky. Removing the IDENTITY property itself wasn't a specifically targeted feature removal, and I would have never fully trusted this approach even back in the ancient days when it was possible.

That said, how about we answer this question instead?

How do I remove the IDENTITY property of a column with minimal or no downtime?

This is a metadata operation only, with no data movement, and will only block other users while the metadata is being updated. But, admittedly, it is a very simplistic example. If you have foreign keys or are using other features like replication, Change Data Capture, Change Tracking, etc. you may need to disable or remove some of those before making this change (I haven't tested all combinations). For foreign keys specifically, see this tip which shows how to generate scripts to drop and re-create all (or selected) foreign key constraints.

Additionally, you will need to update your application code to not expect SQL Server to populate this column, and check any insert or select statements that may be depending on the order of columns or the columns they need to specify. Generally, I would grep your entire code base for any mention of this table.

I would like to encourage anyone coming by to upvote the two connect items. How hard can it be to implement an ALTER COLUMN feature that turns the identity boolean on or off?! Painful to work around.
– usrSep 9 '15 at 21:53

I have to admit, for some reason I thought that ..SWITCH.. only worked for tables that had at least one partition defined.
– RBarryYoungSep 9 '15 at 22:45

Just want to add that SWITCH does not require Enterprise Edition, although table partitioning does.
– Dan GuzmanSep 10 '15 at 1:05