Home Dashboard Directory Help
Search

Change increment values on an identity column by Marc Bamberg


Status: 

Closed
 as Won't Fix Help for as Won't Fix


14
2
Sign in
to vote
Type: Suggestion
ID: 560513
Opened: 5/20/2010 4:21:07 PM
Access Restriction: Public
0
Workaround(s)
view

Description

It is not possible to change the increment value on an identity column once it has been created without dropping and recreating the table. This becomes a huge problem in a 7/24 OLTP system particularly on large tables that have referential constraints on the identity.

It would be very helpful to have this available as it is in some other RDBMS systems.
Details
Sign in to post a comment.
Posted by Andriy Khvyshchun _ on 10/15/2012 at 10:06 AM
I have the same problem - need to replicate on database to 2 servers and make at night the merge of data. I have one existing db, and now will have 2 instances of it. I intend to alter identity increment to 10, so one db will generate 101, 111, 121, ... and other will generate 102, 112, 122, 132. Then, the data can be easily merged using identity insert.
The problem is, that db exists for 2 years, and it has several GB of size. So, now I need to re create each and every table in db and copy data from existing tables to the new ones. I will have to recreate all indexes, foreign keys and primary keys in the system. This will be a huge problem, and the only reason to do it is that I am unable to change increment from the code (using some alter table statement). So, what do you propose me to do - completely re create db, or there are some workarounds?
Posted by Microsoft on 3/20/2012 at 2:58 PM
Thanks for contacting us again regarding this item. I have internally linked to the following item in which we track related changes:

http://connect.microsoft.com/SQLServer/feedback/details/124781/alter-table-syntax-for-changing-column-order

Unfortunately connect will not reflect this.

Thanks
Jan
SQL Server Engine
Posted by Microsoft on 3/20/2012 at 2:58 PM
Thanks for contacting us again regarding this item. I have internally linked to the following item in which we track related changes:

http://connect.microsoft.com/SQLServer/feedback/details/124781/alter-table-syntax-for-changing-column-order

Unfortunately connect will not reflect this.

Thanks
Jan
SQL Server Engine
Posted by Microsoft on 3/20/2012 at 2:58 PM
Thanks for contacting us again regarding this item. I have internally linked to the following item in which we track related changes:

http://connect.microsoft.com/SQLServer/feedback/details/124781/alter-table-syntax-for-changing-column-order

Unfortunately connect will not reflect this.

Thanks
Jan
SQL Server Engine
Posted by darinhorton on 3/17/2012 at 7:57 PM
We use different identity increments and seeds for our customers that are setup for merge replication. The distributor has a positive seed and increment and the subscriber has a negative seed and increment. Without a quick T-SQL method of changing the seed AND increment, i have to go into the table designer for all of our tables that have an identity column and change it - and that can take hours (not due to the change but due to the re-write of the data). Having some easy T-SQL method of changing BOTH of those numbers would be a HUGE time saver. We can see the information in the sys.identity_columns table, so why can't we change them - easily??
Posted by Microsoft on 3/24/2011 at 2:18 PM
Hello Marc,

Thank you for submitting this suggestion, but given its priority relative to the many other items in our queue, it is unlikely that we will actually complete it. As such, we are closing this suggestion as “won’t fix”. If you feel that this is worth reconsidering, feel free to reopen this suggestion or respond to this message and we will take another look.

Jan
SQL Server Engine
Posted by Microsoft on 6/1/2010 at 12:17 PM
Thanks for contacting SQL Server team. I am wondering if you can provide some more details on the applicaion scenario that forced you to change the increment? Was it a change in design issue?

Thanks
Sunil
Sign in to post a workaround.