Autoincrement Primary key generate weird values

Question

I have a table with three columns, and basic SELECT statement would look like:

SELECT TOP 1000 [PatchHistoryId]
      ,CASE 
        WHEN [PatchName] LIKE N'1_%' THEN '1_aaa'
        WHEN [PatchName] LIKE N'2_%' THEN '2_bbb'
        WHEN [PatchName] LIKE N'3_%' THEN '3_ccc'
        WHEN [PatchName] LIKE N'4_%' THEN '4_ddd'
        WHEN [PatchName] LIKE N'5_%' THEN '5_eee'
        WHEN [PatchName] LIKE N'6_%' THEN '6_fff'
        WHEN [PatchName] LIKE N'7_%' THEN '7_ggg'
        WHEN [PatchName] LIKE N'8_%' THEN '8_hhh' END AS PatchName
      ,[CreatedDate]

FROM [PatchHistory]

Result is shown below:

PatchHistoryId | PatchName | CreatedDate

-------------------------------------------

1       1_aaa   2013-07-19 12:50:14.637
2       2_bbb   2013-07-19 12:50:16.570
1002    3_ccc   2013-07-26 08:53:33.557
1003    3_ccc   2013-07-26 08:55:38.600
2002    4_ddd   2013-07-29 11:32:28.320
2003    4_ddd   2013-07-29 11:35:02.123
2004    4_ddd   2013-07-29 14:24:36.297
3002    4_ddd   2013-08-01 09:24:01.537
4002    6_fff   2013-08-06 11:18:29.990
5002    7_ggg   2013-08-08 15:22:56.990
6002    8_hhh   2013-08-20 15:15:35.157
6003    8_hhh   2013-08-20 15:16:40.300
6004    8_hhh   2013-08-20 15:18:00.177
6005    8_hhh   2013-08-20 15:18:00.370
6006    8_hhh   2013-08-20 15:18:00.587
6007    8_hhh   2013-08-20 15:18:00.747
6008    8_hhh   2013-08-20 15:18:00.957
6009    8_hhh   2013-08-20 15:18:01.100
6010    8_hhh   2013-08-20 15:18:01.263
6011    6_fff   2013-08-20 15:18:17.300
6012    8_hhh   2013-08-20 15:28:30.373

Now, first I am found that identity values are extremely high, because I know that I am insert values only 20-30 times, without deleting rows. Assume this, maximum identity value need to be < 100, but is not.

I note that identity in range from 1000-1003 belong to one date, 2000-2004 to another date, 6000-6012 also another date, and to me that is so weird.

It seems that if I insert data tomorow, next identity wouldn't be 6013, but 7000, or something bigger.

What can be trick in this situation.

P.S. In table design, PatchHistoryId is int, Identity seed is 1, Increment is 1.

It seems that SQL jumps to another thousand after every restart of SQL Service :) I already tested that. Is there any good reason why is this better solution instead of old manner identity? — veljasije, Aug 20 '13 at 13:57
There never really was a guarantee that identity would only increment by 1, as the values could be given out within a transaction, then if the transaction were to roll back, the identity values were still used. — tommy_o, Aug 20 '13 at 13:59
OK, that is specific situations, but I don't have transactions, unless you mean on implicit transactions which INSERT statement create. But, if I specified that my seed is 1, and increment is 1, how sql server after restarting can jump over thousand numbers — veljasije, Aug 20 '13 at 14:01

score 6 · Accepted Answer · answered Aug 20 '13 at 14:04

Due to a code change connected with the new sequence feature, identity allocation in SQL 2012 has been changed for performance reasons. Identity values are now preallocated in blocks of 1,000 (for int columns) or 10,000 (for bigint columns).

If the instance is stopped (as in a restart or failover), unused preallocated values are thrown away (because the durable "next" pointer was already set to the next block).

Also, continuous identity values have always been lost through aborted transactions, but since you have nice, daily 1,000 blocks, I would assume a daily restart of your instance as the cause (which you shouldn't do by the way if you don't have a real good reason for it).

Source link, see Michael Duhons entry

score 1 · Answer 2 · answered Aug 20 '13 at 14:47

As already explained by the previous answer this is due to the way that SQL Server 2012 caches identity values which are then lost during a restart of the server.

If this is causing you an issue, you can get around this by using a sequence rather than an identity column and specifying the NO CACHE clause, you then have your column with default value of NEXT VALUE FOR sequence_name.

Bear in mind though that while this will help with the jumps in the identity values, it will impact performance slightly, but if that table you give in the example is representative of the number of inserts you actually do, it shouldn't be much of an issue.

Autoincrement Primary key generate weird values

2 Answers2