Identity column value suddenly jumps to 1001 in sql server

Question

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.

If you care about the numerical values assigned to an identity column, you're doing something *wrong*. You should treat identity values as opaque blobs that you just happen to be able to store in `int` (or other numeric types) columns. — Damien_The_Unbeliever, Jul 11 '13 at 07:11

score 84 · Accepted Answer · edited Jun 20 '20 at 09:12

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

Below is a quote from a Microsoft post replying to this id crisis issue on "connect":If you require the same identity generation semantics as previous versions of SQL Server there are two options available: Use trace flag 272 =>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. — Vishal Gavle, Sep 20 '13 at 06:44
Use a sequence generator with the NO CACHE setting(http://msdn.microsoft.com/en-us/library/ff878091.aspx) =>This will cause a log record to be generated for each generated sequence value. Note that the performance of sequence value generation may be impacted by using NO CACHE. — Vishal Gavle, Sep 20 '13 at 06:44

score 13 · Answer 2 · answered Jul 11 '13 at 07:19

13

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.

answered Jul 11 '13 at 07:19

Mikael Eriksson

136,425
22
210
281

1

How to avoid this situation – Rajaram Shelar Jul 11 '13 at 07:23
@eraj enable TF 272 as described in a comment of the same connect item. – Mikael Eriksson Jul 11 '13 at 07:25
3

@eraj - as I said in a comment to your question, if you *care* about the numerical values, you're using `IDENTITY` wrong. – Damien_The_Unbeliever Jul 11 '13 at 07:25
8

@Damien_The_Unbeliever: No, the person who wrote the system 10+ years ago did it wrong... And I don't have a time machine. – Gerry Coll Nov 07 '13 at 02:45
i have a mild ocd, so jumping the primary key from 6 to 1001 can be frustrating for me – Kokombads Sep 08 '17 at 08:48
If the Identity should ever reach int.Max, will the 'overjumped' ids be used or will it be the end of the world? – Davatar Dec 13 '18 at 03:17
@Davatar There is no automatic going back. Everything stops. – Mikael Eriksson Dec 13 '18 at 05:18

Christopher Bonitz · Answer 3 · 2016-07-26T15:43:59.530

1

I guess you could use sequence instead, sequence gives you 100% complete control, and is in many ways far superior in comparison to identity... Identity is just so damn easy and convenient

http://msdn.microsoft.com/en-us/library/ff878091.aspx

~~As far as i know, when you do a insert with identity and fails, the identity is used anyway,~~ Verified

with sequence you can make it "fill" gaps using cycle. Although, as Amy Barrett is pointing out this is created out of scope of the transaction.

There is a performance optimization when you are using cache that might be useful as well.

edited Jul 26 '16 at 15:43

answered Jul 11 '13 at 10:55

Christopher Bonitz

828
1
10
22

Just in case anyone else is reading this, the Microsoft page (linked in your answer) says _"Sequence numbers are generated outside the scope of the current transaction. They are consumed whether the transaction using the sequence number is committed or rolled back."_ under General Remarks, so I assume the sequence number is used regardless of insert success/failure. – Amy Barrett Jul 26 '16 at 15:08

Identity column value suddenly jumps to 1001 in sql server

3 Answers3

Linked

Related