Take the 2-minute tour ×
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.

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

CREATE TABLE [dbo].[TableName](
    [id] [int] IDENTITY(1,1) NOT NULL
PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

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

2 Answers 2

up vote 4 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
2  
Like I said, I don't care about the gaps. I was just curious as to what might be causing them :) –  Mansfield Mar 31 at 13:54
2  
@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 at 14:22
    
Im one who cares ... What should I use to make a contiguous sequence of numbers? –  Steve yesterday
1  
@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 yesterday

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 at 12:52
3  
@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 at 13:00
3  
Related connect item on gaps: connect.microsoft.com/SQLServer/feedback/details/743300/… –  Shawn Melton Mar 31 at 13:06

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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