Identity cache in sql server 2012 to 2016

Problem :

 From SQL Server 2012 to 2016 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.

Highlighted Points :

Microsoft declares it is a feature rather than a bug and in many scenarios it would be helpful.

  • Microsoft states this is not a bug and does not intend to provide a fix it. It will not be corrected in Patch fixes or service packs.
  • 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.
  • 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.

·         Trace Flag: 272

Function: Disabling the identity cache. It prevents identity gap after restarting SQL Server instance, critical for columns with identity and tinyint and smallint data types.

 

 

Solutions

 

  • Using Sequence
  • Register -t272
  • Upgrade to Sql server 2017

 

1.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:

 

 

CREATE SEQUENCE Id_Sequence

AS INT

START WITH 1

INCREMENT BY 1

MINVALUE 0

NO MAXVALUE

NO CACHE

 

insert into MyTestTable values(NEXT VALUE FOR Id_Sequence, ‘Mr.ravi’);

insert into MyTestTable values(NEXT VALUE FOR Id_Sequence, ‘Mr.suresh’);

 

Thanks,

Sheikvara

91-9840688822

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s