|
Category: SQL Server
view more software Tips and Tricks
Updated: 07/19/2015 04:07 AM
Author: Shiju Mathews Status: Resolved. |
|
From SQL Server 2012 version, when SQL Server instance is restarted then its auto Identity column value is jumped based on identity column datatype. From SQL Server 2012 version, when SQL Server instance is restarted, then table's Identity value is jumped and the actual jumped value depends on identity column data type. If it is integer (int) data type, then jump value is 1000 and if big integer (bigint), then jump value is 10000. From our application point of view, this increment is not acceptable for all the business cases specially when the value shows to the client. This is the special case/issue ships with only SQL Server 2012 and older versions have no such issue. Take a look at the following issue:> Now just restart your SQL Server service. RESULT: Id Name------------------------------ 1 Mr.Bill 2 Mr.Mike 1002 Mr.Bill2 1003 Mr.Mike2 Now you see that after restarting the SQL Server 2012 instance, then identity value starts with 1002. It means it jumped 1000. Previously, I said that we also see if the data type of that identity column is bigint, then it will jump 10000. Solutions 1. Use Sequence: Insted of identity column Sequence for new numbers. 2. Register -t272 to SQL Server Startup Parameter: a. Open SQLServer configuration manager from your server. b. Select SQL Server 2012 instance there right client and select Properties menu. c. You will find a tabbed dialog window. d. You select start up parameters tab. e. ADD -t272 as startup parameter. f. Then restart SQL Server 2012 instance. |