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

Dropbox camera upload

How to backup camera photos ?

Now a day, mobile phone even android or iPhone has a compact size. You can took photos anywhere, so you didn't want to lose that !!, yes you need to backup photos from iphone or android device.

In basically, you can connect your mobile to your PC via usb cable then backup.But now a day, internet speed is faster and cheaper. Wireless upload is more flexible choice now.

You can use some File Manager application that can enable WiFi server.But in this question and answered will let you know about Dropbox.

Dropbox is a free service that lets you upload your photos or any files then can share or access them easily.Dropbox was founded in 2007 so you can trust their reliable.Free storage starting at 2GB up to 18GB (for invite friend, connect to twitter, etc...)

how to upload photos to dropbox