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