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.