3

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
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.

marked as duplicate by Paul White sql-server Jul 22 '15 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
6

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.

  • 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 '14 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 '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
  • 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 Nov 20 '14 at 20:10
  • @AaronBertrand Yes, never allow anyone to delete anything ever (Delete - Deny). Government auditors get very grump when they see gaps in the reporting record to the federal government. I tell them Z28gc2hvdmUgYSBmdWNraW5nIHJha2UgdXAgdGhlaXIgZ29kIGRhbW5lZCBhc3Nlcw== that it's a limitation of the system, that nobody is able to delete rows, and if there was ever any delete: it would be logged with the DELETE TRIGGER. Now if we can just have SQL Server work gooder. – Ian Boyd Jun 20 '17 at 19:31
2

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

1

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]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[ResetOrderIdentityAfterRestart]
AS
BEGIN

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

END

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.

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