Last post Sep 16, 2016 02:06 PM by PatriceSc
Sep 16, 2016 11:54 AM|Krillin|LINK
I have a large number of tables in a SQL 2012 database with identity fields for an application. Microsoft has changed the way identity values are assigned and the values are being incremented by 1000 each time the database server is rebooted. I would like
to maintain a sequential order each time inserts are performed in the application. What is the best method for handling this manually? Should I select the max integer value from the tables and increment by 1 each time?
Sep 16, 2016 12:22 PM|PatriceSc|LINK
See https://msdn.microsoft.com/en-us/library/ms186775.aspx and scroll down to "Consecutive values after server restart or other failures". You can use the new SEQUENCE feature (see
https://msdn.microsoft.com/en-us/library/ff878091.aspx ) with the NOCACHE option.
Not 100% sure but I believe you could and you'll still have gaps if a transaction is not commited. I would avoid to require consecutive numbers (you can't delete an existing row ?). Using a manual solution would be my very last choice.
Edit: or if you don't require number consecutive, just live with it rather than trying to fix something that doesn't cause any issue?
Sep 16, 2016 01:35 PM|Krillin|LINK
I agree to not use consecutive values but it's an existing application and the users are expecting consecutive values.
Sep 16, 2016 02:06 PM|PatriceSc|LINK
Ah usually I never show identity values to users. I use this internally but users are using their own user defined candidate key (a project code, an invoice number etc...).
So here I would switch to SEQUENCE values.