6

I thought it was a bug but after reading this article http://www.codeproject.com/Tips/668042/SQL-Server-2012-Auto-Identity-Column-Value-Jump-Is, I found that it's a new feature of SQL Server 2012.

This feature increments your last identity column value by 1000(ints) for new rows(10000 for bigints) automatically.

enter image description here

I am still trying the solution given in the article but I don't have any problem if this jump happens at client side. Because I am showing hashed version of IDs to client. It's his own demand not mine.

But I am wondering what if the values of these identity columns goes more than the range of the data type (int or bigint)? How it handles the range and size of the column?

Aishwarya Shiva
  • 3,460
  • 15
  • 58
  • 107

3 Answers3

4

Existing Identity columns will fail with "Server: Msg 8115, Level 16, State 1, Line 2 Arithmetic overflow error converting IDENTITY to data type int. Arithmetic overflow occurred." See http://www.sql-server-performance.com/2006/identity-integer-scope/ for discussion.

There isnt a reason to suspect that Identity Jump will have a different behaviour. I would not want it to go and hunt for unused identities in an earlier sequence.

PhillipH
  • 6,182
  • 1
  • 15
  • 25
  • Thanks that link and http://stackoverflow.com/questions/13132939/what-happens-when-i-exhaust-a-bigint-generated-key-how-to-handle-it clears the confusion.Although it's a mysql question but it makes a point.If I am adding the values incrementally starting from zero then it will take 300 million years and 67,108,863 terabytes of free space. Because the max bigint is 9223372036854775807.At 1000 inserts/second that's 106751991167 days worth.Almost 300 million years as explained by @CraigRinger.The only problem is when you insert the maximum limit.But as your article said that will raise the error. – Aishwarya Shiva Apr 30 '16 at 16:04
2

Why don't you use Sequence in MS Server 2012.

Sample Code For Sequence will be as follows and you don't need ADMIN permission to create Sequence.

CREATE SEQUENCE SerialNumber AS BIGINT
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 9999999
CYCLE;
GO

In case if you need to add the leading '0' to Sequence then simple do it with following code :

RIGHT ('0000' + CAST (NEXT VALUE FOR SerialNumber AS VARCHAR(5)), 4) AS SerialNumber
Peter
  • 663
  • 1
  • 7
  • 15
-7

The issue was fixed in the latest version of the SQL Server updates. It was a bug.

If you still want to use the same version, you will have to check your identity at intervals and reseed it with the current identity if they had jumped over 1000s value.

This script can help you to know Seed Values, Increment Values and Current Identity Column Value http://blog.sqlauthority.com/2014/08/29/sql-server-query-to-find-seed-values-increment-values-and-current-identity-column-value-of-the-table-with-max-value-of-datatype-part-2/

My suggestion: Apply resent patches, CU or service pack.

pinaldave
  • 440
  • 6
  • 10
  • 4
    Can you link to any documentation on this fix? I don't see any recent updates to the connect item here https://connect.microsoft.com/SQLServer/feedback/details/739013/failover-or-restart-results-in-reseed-of-identity though it does say the issue with control manager shutdown will be resolved at some point. – Martin Smith Apr 27 '16 at 16:07
  • 5
    What issue was fixed exactly, I don't understand. – ypercubeᵀᴹ Apr 27 '16 at 16:50