Menu
I am using Sql server 2012(Denali). I wonder why all identity column values start from 1001 and so on. At the beginning Identity column starts from 1,2 and so on and adding identity smoothly, but suddenly it jumps to 1001,1002 and onwards for all the table in the database containing identity column. What could be the reason? Please assist.
514
In SQL Server 2012 - MS had introduced 'Identity Cache'. This feature had a bug of auto-incrementing ID column by '1000'. For example, if ID columns are 1, 2 and when an ID Jump happens the next ID column is 1003, instead of '3'. There are workarounds available to fix this issue.
Identity column is not the same as primary key. If you insert some values in id column before setting it to identity then it can generate the same values as you have inserted manually. Show activity on this post. Identity is merely a default new value.
Usually, it occurs when the SQL Server instance is being forced to restart. This skipped gap is particularly depending on the data type of the column, and most of the time, it is possible that column type can be INT, BIGINT, or Numeric.
Microsoft has changed the way they deal with identity values in SQL Server 2012 and as a result of this you can see identity gaps between your records after rebooting your SQL server instance or your server machine. There might be some other reasons for this id gaps, it may be due to automatic server restart after installing an update.
You can use below two choices
Use trace flag 272 o This will cause a log record to be generated for each generated identity value. The performance of identity generation may be impacted by turning on this trace flag.
Use a sequence generator with the NO CACHE setting
Setting Trace Flag 272 on SQL Server 2012 that you are expecting here
Open "SQL Server Configuration Manager"
Click "SQL Server Services" on the left pane
Right-click on your SQL Server instance name on the right pane ->Default: SQL Server(MSSQLSERVER)
Click "Properties"
Click "Startup Parameters"
On the "specify a startup parameter" textbox type "-T272"
Click "Add"
Confirm the changes
129
I believe you have the explanation in a comment to this connect item. Failover or Restart Results in Reseed of Identity
To boost the preformance for high end machines, we introduce preallocation for identity value in 2012. And this feature can be disabled by using TF 272 (then you will get the behaviour from 2008R2).
The identity properties are stored separately in metadata. If a value is used in identity and increment is called, then the new seed value will be set. No operation, including Rollback, Failover, ..... can change the seed value except DBCC reseed. Failover applies for the table object, but no the identity object. So for failover, you can call checkpoint before manual failover, but you may see gap for unplanned cases. If gap is a concern, then I suggest you to use TF 272.
For control manager shutdown, we have a fix for next verion (with another TF). This fix will take care of most control manager shutdown cases.
33
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
