Change increment values on an identity column - by Marc Bamberg

Status : 

  Won't Fix<br /><br />
		Due to several factors the product team decided to focus its efforts on other items.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.


16
2
Sign in
to vote
ID 560513 Comments
Status Closed Workarounds
Type Suggestion Repros 0
Opened 5/20/2010 4:21:07 PM
Access Restriction Public

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. 
Sign in to post a comment.
Posted by Jorge Garcia on 9/5/2014 at 9:50 AM
Changing the increment value of an identity column should be considered a basic operation on any DBMS. Other major database engines support this concept without having to go through the costly process of re-creating the affected tables. Unless it is extremely costly to implement, a real solution to this problem will be very valuable when compared to the cost of current workarounds (on big production databases). I would expect a different position from Microsoft than a simple "Won't fix because priority is not high enough". SQL Server is an enterprise product. Issues like this one deserve attention.
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 Jan [MSFT] 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 Jan [MSFT] 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 Jan [MSFT] 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 Jan [MSFT] 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 Sunil [MSFT] 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