What could cause an auto increment primary key to skip numbers? 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.
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:


Connect # 739013 : Failover or Restart Results in Reseed of Identity


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.