SQL Server 2012 column identity increment jumping

问题:

This question already has an answer here:

• Identity increment is jumping in SQL Server database 6 answers

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?

回答1:

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)

回答2:

I know my answer might be late to the party. But 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].[ResetTableNameIdentityAfterRestart]
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.

回答3:

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

回答4:

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