130

I have a strange scenario in which the auto identity int column in my SQL Server 2012 database is not incrementing properly.

Say I have a table which uses an int auto identity as a primary key it is sporadically skipping increments, for example:

1, 2, 3, 4, 5, 1004, 1005

This is happening on a random number of tables at very random times, can not replicate it to find any trends.

How is this happening? Is there a way to make it stop?

Andy Clark
  • 3,363
  • 7
  • 27
  • 42
  • Don't expect identity values to be dense. For example, a rollback when inserting a row will cause an identity value to be "consumed" leaving a gap in the numbers. – HABO Jan 04 '13 at 19:03
  • 15
    I was aware that (for example inserting a record that fails) the next number in the identity will be used and lost, it was just slightly alarming when its skips over 1000 – Andy Clark Jan 04 '13 at 19:54
  • If you insert 2000 records in a single statement, and the 1000th fails, you will lose 1000 from your sequence. I once lost 100,000 once in a transaction I rolled back. It's either that or block all inserts until the transaction commits. – Ben May 08 '14 at 20:46
  • What the hell is this. We started experiencing the same and it is screwing up with the way manage our data. – Allen King Jul 21 '14 at 19:04
  • 4
    While it is very helpful to know this is how Sql Server 2012 now works, it is a real problem. The proposed solutions won't work for me as I do not have access to the hosted Sql Server Express server. I also cannot simply use a Sequence Generator as I need consecutive numbers - say for Invoices where a jump of 1,000 is unacceptable or incrementing numbers across several tables won't work. Plus, I may add rows from a Microsoft access front end, so I can't just update the INSERT statement to handle this. I'm considering a non primary key updated in an After Insert trigger. Any better ideas? – MicrosoftAccessPros.com Apr 28 '15 at 20:12

3 Answers3

82

This is all perfectly normal. Microsoft added sequences in SQL Server 2012, finally, i might add and changed the way identity keys are generated. Have a look here for some explanation.

If you want to have the old behaviour, you can:

  1. use trace flag 272 - this will cause a log record to be generated for each generated identity value. The performance of identity generation may be impacted by turning on this trace flag.
  2. use a sequence generator with the NO CACHE setting (http://msdn.microsoft.com/en-us/library/ff878091.aspx)
Mithrandir
  • 24,869
  • 6
  • 50
  • 66
  • 108
    it's all perfectly normal until you run out of ints – Simon_Weaver Jul 03 '13 at 02:12
  • 5
    I have a 'traditional' identity column with an increment set to 42. The column value incremented by 41706 (eek!) which just happens to be 993*42 – Simon_Weaver Jul 03 '13 at 02:41
  • 1
    @Simon_Weaver how often do you restart your sql server? I have been running several instances for over a year now, not a single issue with sequences. – Mithrandir May 08 '14 at 19:24
  • 4
    we never restart the server and the sequence jumped!! – JYousef Oct 23 '14 at 15:24
  • 80
    With "features" like this, who needs bugs? – HerrimanCoder Mar 31 '15 at 14:56
  • Using Sequence comes with its own problems: "General Remarks Sequence numbers are generated outside the scope of the current transaction. They are consumed whether the transaction using the sequence number is committed or rolled back." https://learn.microsoft.com/en-us/sql/t-sql/statements/create-sequence-transact-sql – Tikall Sep 20 '17 at 12:14
  • In the link for explanation is not explanation. :( – Gh61 Mar 28 '18 at 12:09
  • this article [here](https://www.ontechnology.com.ly/2019/03/01/identity-column-big-jump-and-its-solution-for-sql-server-2014/) explains how to solve it for sql server2014 but I think it is the same problem – JBALI Jul 16 '19 at 00:09
4

Got the same problem, found the following bug report in SQL Server 2012 If still relevant see conditions that cause the issue - there are some workarounds there as well (didn't try though). Failover or Restart Results in Reseed of Identity

yoosha
  • 681
  • 2
  • 7
  • 12
1

While trace flag 272 may work for many, it definitely won't work for hosted Sql Server Express installations. So, I created an identity table, and use this through an INSTEAD OF trigger. I'm hoping this helps someone else, and/or gives others an opportunity to improve my solution. The last line allows returning the last identity column added. Since I typically use this to add a single row, this works to return the identity of a single inserted row.

The identity table:

CREATE TABLE [dbo].[tblsysIdentities](
[intTableId] [int] NOT NULL,
[intIdentityLast] [int] NOT NULL,
[strTable] [varchar](100) NOT NULL,
[tsConcurrency] [timestamp] NULL,
CONSTRAINT [PK_tblsysIdentities] PRIMARY KEY CLUSTERED 
(
    [intTableId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,  ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

and the insert trigger:

-- INSERT --
IF OBJECT_ID ('dbo.trgtblsysTrackerMessagesIdentity', 'TR') IS NOT NULL
   DROP TRIGGER dbo.trgtblsysTrackerMessagesIdentity;
GO
CREATE TRIGGER trgtblsysTrackerMessagesIdentity
ON dbo.tblsysTrackerMessages
INSTEAD OF INSERT AS 
BEGIN
    DECLARE @intTrackerMessageId INT
    DECLARE @intRowCount INT

    SET @intRowCount = (SELECT COUNT(*) FROM INSERTED)

    SET @intTrackerMessageId = (SELECT intIdentityLast FROM tblsysIdentities WHERE intTableId=1)
    UPDATE tblsysIdentities SET intIdentityLast = @intTrackerMessageId + @intRowCount WHERE intTableId=1

    INSERT INTO tblsysTrackerMessages( 
    [intTrackerMessageId],
    [intTrackerId],
    [strMessage],
    [intTrackerMessageTypeId],
    [datCreated],
    [strCreatedBy])
    SELECT @intTrackerMessageId + ROW_NUMBER() OVER (ORDER BY [datCreated]) AS [intTrackerMessageId], 
    [intTrackerId],
   [strMessage],
   [intTrackerMessageTypeId],
   [datCreated],
   [strCreatedBy] FROM INSERTED;

   SELECT TOP 1 @intTrackerMessageId + @intRowCount FROM INSERTED;
END
MicrosoftAccessPros.com
  • 306
  • 3
  • 12