Identity Jumping Increment MS SQL Server 2012

Jumping identity problem

If you create table in MS SQL Server 2012 by using design mode then set identity column. Sometime you will see your identity column jumping !!

Example 1, 2, 3, 12, 13 ,14

It 's OK if your data is not much. But if you have big data, it's will faster reach limit of column.
See this both script and see what's happen.

Script 1

CREATE TABLE [dbo].[testAuto](
 [autoID] [tinyint] PRIMARY KEY IDENTITY(1,1) NOT NULL,
 [Name] [varchar](10) NOT NULL
) ON [PRIMARY]

Script 2

CREATE SEQUENCE s1 AS INT START WITH 1 NO CACHE;
CREATE TABLE [dbo].[testSequence](
 [autoID] [tinyint] DEFAULT NEXT VALUE FOR s1,
 [Name] [varchar](10) NOT NULL
) ON [PRIMARY]

Script 1 is general table that can happen when created by design mode.
Script 2 is alter way that basic users may not know.

Then try to insert data then see result.

insert into testAuto(Name) values ('xxx')
select top 10 * from testAuto order by autoID desc
insert into testSequence(Name) values ('xxx')
select top 10 * from testSequence order by autoID desc

Result (Execute 3 times)

3 xxx
2 xxx
1 xxx

3 xxx
2 xxx
1 xxx

That's normally result no problem. But test restart your server.

Result (Execute again 3 times)

14 xxx
13 xxx
12 xxx
3 xxx
2 xxx
1 xxx

6 xxx
5 xxx
4 xxx
3 xxx
2 xxx
1 xxx

So, you will see your identity column is jumping !! In this example use tinyint, so normally you can insert 128 values. But with jumping you may insert about 3-50 values (If in long term many many restart)

Solution

Method 1
Use sequence instead of identity (example script 2)
But not recommend because you cannot use design mode, just for coding and must do every time.

Method 2
Add Startup Parameters "-T272" Recommend
Just one time config, then always apply to new table

1. Open SQL Server Configuration Manager
2. Left click on SQL Server Services
3. Right click on SQL Server then select properties
4. Add "-T272" in Startup Parameters
5. Restart SQL Server

Test again by drop table.

drop table testAuto
drop table testSequence
drop sequence s1

Then

insert into testAuto(Name) values ('xxx')
select top 10 * from testAuto order by autoID desc
insert into testSequence(Name) values ('xxx')
select top 10 * from testSequence order by autoID desc

Result (No problem even restart)

6 xxx
5 xxx
4 xxx
3 xxx
2 xxx
1 xxx

6 xxx
5 xxx
4 xxx
3 xxx
2 xxx
1 xxx

Note : This article apply for MS SQL Server 2012 SP1