7

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.

Aaron Bertrand
  • 181,950
  • 28
  • 405
  • 624
Mansfield
  • 1,061
  • 8
  • 20
  • 33

3 Answers3

8

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.

Community
  • 1
Aaron Bertrand
  • 181,950
  • 28
  • 405
  • 624
5

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

datagod
  • 7,141
  • 4
  • 38
  • 58
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.

Jeyara
  • 111
  • 3