Allow enabling and disabling of a column's IDENTITY property - by Adam Machanic

Status : 


Sign in
to vote
ID 252226 Comments
Status Active Workarounds
Type Suggestion Repros 1
Opened 1/16/2007 10:51:11 AM
Duplicates 294193 Access Restriction Public


Sometimes IDENTITY columns need to be cleaned up a bit -- their values updated or re-ordered, for instance.  This can occur when data replication strategies fail, or when unexpected data was inserted.  This is a somewhat rare occurrence, but when it does happen it can be extremely painful due to the fact that IDENTITY columns cannot be updated, and the property cannot be enabled or disabled.  As a result, in most cases doing what should be a simple update requires creation of a new table.

Likewise, we sometimes want to be able to turn an existing column into an IDENTITY column.  The same problem exists there -- it simply can't be done.
Sign in to post a comment.
Posted by Anton Plotnikov on 6/18/2013 at 3:17 AM
Sequences were introduced in SQL 2012. But we cannot use it because it is an impossible to replace identity to sequence without recreating table. Our database contains a lot of foreign keys, indexes etc. In other words "simple dropping identity property (not column data!)" leads to total DB reconstruction. Proposed suggestion can simplify such migration dramatically.
Posted by prk on 5/2/2013 at 8:10 PM
I just realised how painful having identity columns !! we recently wanted to merge two client databases (their businesses merging) and it became a nightmare!! Because we used identities as PK and we could not update PKs because of this sql restriction. we wanted to assign a range IDs to one client's database and bring them to second database. only if we could update identity column it could have been much simpler!
Posted by Paul White NZ on 2/4/2010 at 5:16 PM
I have added a partial workaround using ALTER TABLE...SWITCH.
I do agree that this would be a useful extension to ALTER TABLE ALTER COLUMN though...
Posted by Daniel Smith on 10/16/2009 at 4:00 AM
@way0utwest - the workaround definitely does work in 2005 - I used it just the other day!
Posted by way0utwest on 2/21/2008 at 8:08 AM
Workaround doesn't work in 2000 or 2005.

I would rate updates more important than turn on/off, but both would be very handy.
Posted by David Avsajanishvili on 7/5/2007 at 12:27 AM
IDENTITY columns CAN be updated! See workaround...
Posted by Microsoft on 1/18/2007 at 10:51 AM
Thank you for the feedback, we'll look into ways to improve on the scenario you describe.


- Christian Kleinerman