当 IDENTITY_INSERT 为 ON 时，如何强制 SQL Server 2008 不更改 AUTOINC_NEXT 值？
我有关于 IDENTITY_INSERT 的问题。当您将其更改为ON时，SQL Server会自动将AUTOINC_NEXT值更改为最后插入的值作为标识。
因此，如果您只有一行 ID = 1 并在 IDENTITY_INSERT 为 ON 时插入 ID = 100 的行，那么下一个插入行的 ID = 101。我希望它为 2，而不需要重新设定种子。
SQL Server Compact 3.5 中已存在此类行为。是否可以强制 SQL Server 2008 在使用 IDENTITY_INSERT = ON 进行插入时不更改 AUTOINC_NEXT 值？
I got question about IDENTITY_INSERT. When you change it to ON, SQL Server automatically changes AUTOINC_NEXT value to the last inserted value as identity.
So if you got only one row with ID = 1 and insert row with ID = 100 while IDENTITY_INSERT is ON then next inserting row will have ID = 101. I'd like it to be 2 without need to reseed.
Such behaviour already exists in SQL Server Compact 3.5. Is it possible to force SQL Server 2008 to not change AUTOINC_NEXT value while doing insert with IDENTITY_INSERT = ON ?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
我不知道有什么方法可以防止这种行为 - 毕竟，无论如何，这是一件谨慎的事情！如果您已经设置了值
x（无论该值是什么），则不应将种子值设置为低于x，否则，您必须遇到在 IDENTITY 列中获取已经存在的值的情况 - 这不是一个好地方！但如果必须的话，您可以在完成插入后使用以下命令重新设置 IDENTITY 列的种子：
您可以在其中将任意值设置为新的种子值（此处为：300）。当然，您需要格外小心，不要通过将重新种子值设置得太低来创建任何重复项。
I'm not aware of any way to prevent this behavior - after all, it's a prudent thing to do, anyway! If you've already set a value
x(whatever that value might be), you shouldn't leave your seed value lower thanxsince otherwise, you're bound to run into getting a value in your IDENTITY column that's already there - not a good place to be!But if you must, you can re-seed your IDENTITY column after you're done with your inserts using:
where you can set any arbitrary value as your new seed value (here: 300). Of course, you need to be extra careful not to create any duplicates by setting the reseed value too low.
此外，如果您执行重新设定种子并且您的标识列也是主键，则当设置 IDENTITY_INSERT 表名 ON 时返回到最初插入的值时，您将遇到 PK 冲突。另一件需要考虑的事情。
Additionally, if you do a reseed and your identity column is also a primary key, when you get back to the originally inserted value when IDENTITY_INSERT tablename ON was set you will get a PK violation. Another thing to think about.
你为什么要这么做？首先，在生产系统中几乎不应该将身份插入设置为ON。如果你跳过一些会有什么不同呢？您不能指望身份在任何情况下都不会跳过，因为如果您进行回滚，它就会跳过。
Why would you want to do that? In the first place, there should be almost no occasions where you set identity insert to ON in a production system. And what difference would it make if you skip some? You can't count on identities not skipping in any event since it will skip if you do a rollback.