Allow enabling and disabling of a column's IDENTITY property
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.
Upvotes: 60<=-=Jan 18 2007 10:51AM=-=>
Thank you for the feedback, we’ll look into ways to improve on the scenario you describe.
- Christian Kleinerman<=-=Jul 5 2007 12:27AM=-=>
IDENTITY columns CAN be updated! See workaround…<=-=Feb 21 2008 8:08AM=-=>
Workaround doesn’t work in 2000 or 2005.
I would rate updates more important than turn on/off, but both would be very handy.<=-=Oct 16 2009 4:00AM=-=>
@way0utwest – the workaround definitely does work in 2005 – I used it just the other day!<=-=Feb 4 2010 5:16PM=-=>
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…
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!<=-=Jun 18 2013 3:17AM=-=>
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.
Mike Doerfler commented
please introduce something to allow an identity column to be switched to a sequence. Drop/Recreate/Switch with indexes and foreign keys is painful if you have lots of tables using identity you would like converted to identity