SQL Server 2012 列标识增量在第 7 个条目上从 6 跳到 1000+
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14162648/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Server 2012 column identity increment jumping from 6 to 1000+ on 7th entry
提问by Andy Clark
I have a strange scenario in which the auto identity int column in my SQL Server 2012 database is not incrementing properly.
我有一个奇怪的场景，我的 SQL Server 2012 数据库中的 auto identity int 列没有正确递增。
Say I have a table which uses an int auto identity as a primary key it is sporadically skipping increments, for example:
假设我有一个使用 int 自动标识作为主键的表，它偶尔会跳过增量，例如：
1, 2, 3, 4, 5, 1004, 1005
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?
这是怎么回事？有没有办法让它停止？
采纳答案by Mithrandir
This is all perfectly normal. Microsoft added sequencesin SQL Server 2012, finally, i might add and changed the way identity keys are generated. Have a look herefor some explanation.
这一切都很正常。Microsoftsequences在 SQL Server 2012 中添加，最后，我可能会添加和更改身份密钥的生成方式。看看这里的一些解释。
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)
- 使用跟踪标志 272 - 这将导致为每个生成的标识值生成一个日志记录。打开此跟踪标志可能会影响身份生成的性能。
- 使用带有 NO CACHE 设置的序列生成器 ( http://msdn.microsoft.com/en-us/library/ff878091.aspx)
回答by yoosha
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
遇到了同样的问题，在 SQL Server 2012 中发现了以下错误报告 如果仍然相关，请查看导致问题的条件 - 也有一些解决方法（虽然没有尝试）。 故障转移或重新启动导致身份重新种子
回答by MicrosoftAccessPros.com
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.
虽然跟踪标志 272 可能适用于许多，但它绝对不适用于托管的 Sql Server Express 安装。所以，我创建了一个身份表，并通过 INSTEAD OF 触发器使用它。我希望这可以帮助其他人，和/或让其他人有机会改进我的解决方案。最后一行允许返回添加的最后一个标识列。由于我通常使用它来添加单行，因此可以返回单个插入行的标识。
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
