SQL Server奇怪的身份增量 [英] Sql server strange Identity increment

查看：84 发布时间：2020/9/17 21:02:42 sql-server azure-sql-database

本文介绍了SQL Server奇怪的身份增量的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我在SQL Azure上设置了此表

I have this table setup at SQL Azure

CREATE TABLE [dbo].[Sl](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [PublicId] [uniqueidentifier] NOT NULL,
 CONSTRAINT [PrimaryKey_ba033f1f-ac1b-4616-8591-fcd47fe0f63d] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON),
 CONSTRAINT [PublicId_UNIQUE] UNIQUE NONCLUSTERED 
(
    [PublicId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)

非常奇怪的是，ID以意外的方式递增.

The very strange thing is that the ID gets incremented in a unexpected way.

有人知道如何正常增加此值吗? 这样数字就不会那样跳? 知道如何正常增加此增量吗? 这样数字就不会那样跳动了?

Any idea how to make this increment normally? So that the numbers don't jump like that? Any idea how to make this increment normally? So that the numbers don't jump like that?

推荐答案

这是一个已知问题. Connect上报告了两个相关的项目.这实际上是按设计关闭的，但是我认为发表评论的工程师并不真正理解这个问题:

This is a known issue. There are two related items reported on Connect. This one is actually closed as by design, but I don't think the engineer who left the comment really understood the issue:

http://connect .microsoft.com/SQLServer/feedback/details/743300/identity-column-jumps-by-seed-value

此(较早！)错误处于活动状态:

This (earlier!) bug is active:

http ://connect.microsoft.com/SQLServer/feedback/details/739013/failover-or-restart-results-in-reseed-of-identity

后者有一个有趣的解决方法，而另一些则引起了我的注意. 上的主要解决方法>建议使用的欺骗方法适用于您自己的盒子，但是祝您好运在Azure中运行跟踪标志(或创建启动过程作为建议的连接项之一).使用不带缓存的SEQUENCE可能是一个可行的选择，但是您应该验证这些折衷是否值得避免无意义的替代编号之间的差异.那里的另一个答案建议在每次插入后都发出一个CHECKPOINT.哎呀.

The latter has one interesting workaround, and others that raise my eyebrows. The primary workaround on the suggested dupe would be fine for your own box, but good luck running a trace flag in Azure (or creating a startup proc as one of the connect items suggested). Using a SEQUENCE with no cache may be a viable option, but you should validate that the trade-offs are worth avoiding gaps in a meaningless, surrogate number. The other answer there suggests issuing a CHECKPOINT after every insert. Ouch.

最后，如果您想要一个没有间隙的序列，请停止使用IDENTITY-这不是它设计的目的.

In the end, if you want a sequence without gaps, stop using IDENTITY - that's not what it was designed to do.

这篇关于SQL Server奇怪的身份增量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文

SQL Server奇怪的身份增量 [英] Sql server strange Identity increment

问题描述

推荐答案

相关文章

数据库最新文章

热门教程

热门工具

登录关闭

SQL Server奇怪的身份增量 [英] Sql server strange Identity increment

问题描述

推荐答案

相关文章

数据库最新文章

热门教程

热门工具

登录 关闭

登录关闭