Sign up ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

This question already has an answer here:

I have a table created with the following T-SQL:

CREATE TABLE [dbo].[TableName](
    [id] [int] IDENTITY(1,1) NOT NULL
    [id] ASC

I took a look at this table today and noticed the id column was skipping values:

1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 1027, 1028, 1029, 1030, 1031, 1032, 1033, 1034, 1035, 2034, 2035, 3034

I'm not worried about this or anything (my application doesn't care what the primary key is), I was just curious about what might cause this to occur. It does seem there's a sort of pattern, of jumping 1000 every time a "jump" happens.

My application using entity framework to insert these rows, if that makes any difference.

share|improve this question

marked as duplicate by Paul White sql-server Jul 22 at 6:50

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

Just a brief time googling the entity framework piece can have an affect on the identify seed being altered/overwritten. – Shawn Melton Mar 31 '14 at 13:05
In EF are you using a connection string that includes User Instance and AttachDbFileName settings? – Aaron Bertrand Mar 31 '14 at 13:33

3 Answers 3

up vote 5 down vote accepted

There are many things that can cause gaps in an IDENTITY column (rollbacks, deletes), but in this case due to the jump I suspect it is this bug - caused by the changes to IDENTITY with the introduction of SEQUENCE:

So I bet that if you look in SQL Server's error logs, the rows associated with these jumps were inserted shortly after a SQL Server service restart, a database was detached/re-attached, etc.

Really, though, why do you care about gaps? You shouldn't. If you need a contiguous sequence of numbers, stop using IDENTITY.

share|improve this answer
Like I said, I don't care about the gaps. I was just curious as to what might be causing them :) – Mansfield Mar 31 '14 at 13:54
@Mansfield that's not just for you, but other readers too... since most people who notice this, complain about it, or search for it, do care about gaps and do expect IDENTITY to provide magical sequences. – Aaron Bertrand Mar 31 '14 at 14:22
Im one who cares ... What should I use to make a contiguous sequence of numbers? – user52597 Nov 20 '14 at 19:12
@Steve You can use a serializable transaction to make the next row max(current id) + 1. But how are you going to prevent gaps? Never let anyone delete anything ever? – Aaron Bertrand Nov 20 '14 at 20:10
BillingID int primary key identity and BillingID int primary key identity(1,1) , are both same ? will both cause auto-increment, what is correct syntax to create table sql with primary auto_increment ? – stom Oct 26 at 9:19

When inserts are rolled back, the identity values are NOT reset. This is one explanation of the so called "skipped" values.

share|improve this answer
Interesting, didn't know that. I can't think of too many situations wherein that would have happened here though; and definitely not in the order of a thousand times... – Mansfield Mar 31 '14 at 12:52
@Mansfield that's one reason. There are several others and some depend on version. SQL-Server 2012 is known to have these 1000 or bigger gaps: Connect item: Failover or Restart Results in Reseed of Identity – ypercube Mar 31 '14 at 13:00
Related connect item on gaps:… – Shawn Melton Mar 31 '14 at 13:06

I know my answer might be late. Yes, this used to happen when SQL server is restarted. See here for more information (as given in other answers). With SQL Server 2014, it has been fixed (identity wont increased by 1000) only if there was a planned SQL Server restart.

I have solved in another way by adding a start up stored procedure in SQL Server 2012.

Create a following stored procedure in master DB.

USE [master]

ALTER PROCEDURE [dbo].[ResetOrderIdentityAfterRestart]

begin TRAN
    declare @id int = 0
    SELECT @id =  MAX(id) FROM [DatabaseName].dbo.[TableName]
    --print @id
    DBCC CHECKIDENT ('[DatabaseName].dbo.[TableName]', reseed, @id)


Then add it in to Start up by using following syntax.

EXEC sp_procoption 'ResetOrderIdentityAfterRestart', 'startup', 'on';

This is a good idea if you have few tables. but if you have to do for many tables, this method still works but not a good idea.

share|improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.