SQL Server 2012 column identity increment jumping from 6 to 1000+ on 7th entry

 

This question already has an answer here:

  • Identity increment is jumping in SQL Server database 6 answers

    • 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:

  • 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.
  • use a sequence generator with the NO CACHE setting (http://msdn.microsoft.com/en-us/library/ff878091.aspx)

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

    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

    链接地址: http://www.djcxy.com/p/94434.html

    上一篇: 在SQL Server中CREATE TABLE IF NOT EXISTS等效

    下一篇: 第7项中的SQL Server 2012列标识增量从6跳到1000+