Click here to Skip to main content
16,015,973 members
Articles / Programming Languages / SQL
Tip/Trick

SQL Server 2012 Auto Identity Column Value Jump Issue

Rate me:
Please Sign up or sign in to vote.
4.85/5 (56 votes)
14 Oct 2013CPOL3 min read 345K   34   47
From SQL Server 2012 version, when SQL Server instance is restarted then its auto Identity column value is jumped based on identity column datatype.

Introduction

From SQL Server 2012 version, when SQL Server instance is restarted, then table's Identity value is jumped and the actual jumped value depends on identity column data type. If it is integer (int) data type, then jump value is 1000 and if big integer (bigint), then jump value is 10000. From our application point of view, this increment is not acceptable for all the business cases specially when the value shows to the client. This is the special case/issue ships with only SQL Server 2012 and older versions have no such issue.

Background

A few days ago, our QA Engineer claims that one of our table's identity column jumped 10000. That means the last identity value of that table was 2200 now it is 12001. In our business logic is like that the value shows to the client and it will not be accepted by the client. So we must solve the issue.

Using the Code

The first time, we all are surprised and confused as to how it is possible? We usually do not insert any value in identity column (insert value to identity column is possible). The identity value is maintained by SQL Server itself. One of our core team members started investigation the issue and found out the solution. Now, I want to elaborate the issue and solution that was found out by my colleague.

How to Reproduce That?

You need to setup SQL Server 2012 and create a test database. Then create a table with auto identity column:

SQL
create table MyTestTable(Id int Identity(1,1), Name varchar(255));

Now insert 2 rows there:

SQL
insert into MyTestTable(Name) values ('Mr.Tom');
insert into MyTestTable(Name) values ('Mr.Jackson'); 

You see the result:

SQL
SELECT Id, Name FROM MyTestTable; 

Image 1

The result is as expected. Now just restart your SQL Server service. There are various ways in which you can do it. We did it from SQL Server management studio.

Image 2

Now, insert another 2 rows to the same table again:

SQL
insert into MyTestTable(Name) values ('Mr.Tom2');
insert into MyTestTable(Name) values ('Mr.Jackson2');

Now see the result:

SQL
SELECT Id, Name FROM MyTestTable;

Image 3

Now you see that after restarting the SQL Server 2012 instance, then identity value starts with 1002. It means it jumped 1000. Previously, I said that we also see if the data type of that identity column is bigint, then it will jump 10000.

Is it really a bug?

Microsoft declares it is a feature rather than a bug and in many scenarios it would be helpful. But in our case, it would not be acceptable because that number is shown to the client and the client will be surprised to see that new number after jump and the new number depends on how many times SQL Server is restarted. If it is not visible to the client, then it might be acceptable so that the number is used internally.

Solutions

If we are not interested in this so called feature, then we can do two things to stop that jump.

  • Using Sequence
  • Register -t272 to SQL Server Startup Parameter

Using Sequence

First, we need to remove Identity column from tables. Then create a sequence without cache feature and insert number from that sequence. The following is the code sample:

SQL
CREATE SEQUENCE Id_Sequence
    AS INT
    START WITH 1
    INCREMENT BY 1
    MINVALUE 0
    NO MAXVALUE
   NO CACHE
SQL
insert into MyTestTable values(NEXT VALUE FOR Id_Sequence, 'Mr.Tom'); 
SQL
insert into MyTestTable values(NEXT VALUE FOR Id_Sequence, 'Mr.Jackson'); 

Register -t272 to SQL Server Startup Parameter

Open SQLServer configuration manager from your server. Select SQL Server 2012 instance there right client and select Properties menu. You will find a tabbed dialog window. You select start up parameters tab from there and register -t272. Then restart SQL Server 2012 instance again and see the difference:

Startup Parater

Points of Interest

If too many tables contain identity column to your database and all contain existing values, then it is better to go for solution 2. Because it is a very simple solution and its scope is server wise. This means if you add SQL Server 2012 parameter -t272 there, then it will affect all your databases there. If you want to create a new database and you need auto generated number field, then you can use solution 1, that means use sequence value to a column instead of auto Identity value. There are so many articles you can find online about when you will use auto identity column when using sequence and advantages/disadvantages of each other. I hope you will read all those and take the appropriate decision.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Architect
Bangladesh Bangladesh
How do I describe myself to you? How can I explain that this is true?
I am who I am because of you! My work I love you !!

Comments and Discussions

 
SuggestionAnother way to solve this "feature" Pin
Member 141091563-Jan-23 6:16
Member 141091563-Jan-23 6:16 
Questioncan i do both? Pin
tarik0777-Apr-19 20:42
tarik0777-Apr-19 20:42 
Praisereally a great explanation. Pin
No_Rules26-Nov-18 19:03
No_Rules26-Nov-18 19:03 
QuestionWhether this jump is only for sql2012 or above too? Pin
Sankara Pandian13-May-18 9:41
professionalSankara Pandian13-May-18 9:41 
AnswerRe: Whether this jump is only for sql2012 or above too? Pin
No_Rules26-Nov-18 19:05
No_Rules26-Nov-18 19:05 
SuggestionAlternate solution Pin
Arkadeep De12-Apr-18 1:09
professionalArkadeep De12-Apr-18 1:09 
SuggestionSet -T272 in Startup Parameters Pin
Nadim Hossain Sonet25-Oct-17 22:43
Nadim Hossain Sonet25-Oct-17 22:43 
Question-T272 start-up parameter Pin
Member 133534289-Aug-17 1:43
Member 133534289-Aug-17 1:43 
PraiseThanks Pin
arpita.poojary20-Jul-17 21:36
arpita.poojary20-Jul-17 21:36 
PraiseThanks Pin
gijith18-Apr-17 20:59
professionalgijith18-Apr-17 20:59 
GeneralMy vote of 4 Pin
Umesh AP6-Dec-16 23:51
Umesh AP6-Dec-16 23:51 
QuestionNice article! Thx. You meant 12201 not 12001 right? Pin
Bartosh6-Apr-16 23:22
Bartosh6-Apr-16 23:22 
Suggestion-T272 not -t272 Pin
Member 841426311-Feb-16 8:22
Member 841426311-Feb-16 8:22 
QuestionThanks, Pin
Shail Mishra27-Oct-15 23:46
Shail Mishra27-Oct-15 23:46 
QuestionLocalDB with no access to SQL Server Startup Parameter Pin
William Chan Choon Huat7-Sep-15 21:49
professionalWilliam Chan Choon Huat7-Sep-15 21:49 
QuestionThanks Pin
Reivaj81020-Aug-15 7:31
Reivaj81020-Aug-15 7:31 
QuestionThankx Pin
manish_kumar_gupta21-Jul-15 19:14
manish_kumar_gupta21-Jul-15 19:14 
QuestionThanks Pin
Ëlson M. Silva27-Apr-15 6:53
Ëlson M. Silva27-Apr-15 6:53 
QuestionNothing changed. Pin
mtmutlu11-Mar-15 0:07
mtmutlu11-Mar-15 0:07 
AnswerRe: Nothing changed. Pin
mtmutlu11-Mar-15 0:17
mtmutlu11-Mar-15 0:17 
QuestionJump not found is table is not in use at restart. Pin
Jitesh Hirani29-Oct-14 19:37
professionalJitesh Hirani29-Oct-14 19:37 
AnswerRe: Jump not found is table is not in use at restart. Pin
S. M. Ahasan Habib2-Nov-14 18:55
professionalS. M. Ahasan Habib2-Nov-14 18:55 
QuestionThanks! Pin
Aaamir_Ghanchi28-Oct-14 11:15
Aaamir_Ghanchi28-Oct-14 11:15 
AnswerRe: Thanks! Pin
S. M. Ahasan Habib28-Oct-14 16:44
professionalS. M. Ahasan Habib28-Oct-14 16:44 
GeneralMy vote of 5 Pin
Vihang Shah17-Sep-14 0:12
Vihang Shah17-Sep-14 0:12 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.