How new Identity Jump feature of Microsoft SQL Server 2012 handles the range of data type?

Question

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.

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?

Answer 1

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 

Answer 2

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.