Menu
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?
509
Identity columns can be used for generating key values. The identity property on a column guarantees the following: Each new value is generated based on the current seed & increment. Each new value for a particular transaction is different from other concurrent transactions on the table.
An identity column is a numeric column in a table that is automatically populated with an integer value each time a row is inserted. Identity columns are often defined as integer columns, but they can also be declared as a bigint, smallint, tinyint, or numeric or decimal as long as the scale is 0.
In SQL Server 2012 - MS had introduced 'Identity Cache'. This feature had a bug of auto-incrementing ID column by '1000'. For example, if ID columns are 1, 2 and when an ID Jump happens the next ID column is 1003, instead of '3'. There are workarounds available to fix this issue.
Reset the Identity Value Using the DBCC CHECKIDENT Method : Here, to reset the Identity column in SQL Server you can use DBCC CHECKIDENT method. Syntax : DBCC CHECKIDENT ('table_name', RESEED, new_value); Note : If we reset the existing records in the table and insert new records, then it will show an error.
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
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.
140
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
