Autoincrement Primary key generate weird values
sql-server, sql-server-2012
Solution
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
Problem
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.