如何更改记录中的Id字段？ [英] How do I change an Id field in a record?
问题描述
我有一个SQL数据库，其Id字段已经变得混乱。它从688跳到1687并继续从那里开始。我无法弄清楚为什么跳。如何在数据库中移动并更改Id编号以匹配记录编号并重置下一个Id编号的计数？任何帮助将不胜感激。
I have a SQL database that has an Id field that has gone haywire. It has jumped from 688 to 1687 and continued from there. I can't figure out why the jump. How can I move through the database and change the Id number to match the record number and reset the count for the next Id number? Any help will be greatly appreciated.
推荐答案
如果它是自动递增 - 即它在INSERT一个值时自己分配一个新值，那么它是一个IDENTITY字段，那个行为是设计的：当你删除现有的行时，ID号码不会被重用，所以你会得到空白。
我不会改变它 - ID号不应该改变，它应该是一个标识行的永久唯一值。如果你想要一个始终严格顺序的值，那么使用ROWNUMBER函数和你的select来生成它而不是改变ID值。
如果您必须更改它，然后最安全的方法是创建一个具有非身份ID值的新表，并复制每一行，重新编号ID。最后，删除旧表并重命名新表。
但是如果你使用任何外键值，你可能会给自己造成很大的问题（你可能会在一个月内再次出现漏洞或者两个......）
If it's auto incrementing - i.e. it assigns a new value itself when you INSERT a value, then it's an IDENTITY field, and that behaviour is by design: when you remove existing rows, the ID numbers are not reused so you get gaps.
I wouldn't change it - the ID number isn't supposed to change, it's supposed to be a "permanent" unique value which identifies the row. If you want a value which is always in strict sequence, then use the ROWNUMBER function with your select to generate it on the fly instead of altering the ID value.
If you must change it, then the safest way is to create a new table with a non-identity ID value, and copy each row over, renumbering the ID's as you go. At the end, delete the old table and rename the new.
But if you use any foreign key values, you will likely cause enormous problems for yourself (and you'll probably have holes again in a month or two...)
您使用的是SQL 2012或更高版本吗？如果是这样，请此错误报告 [ ^ ]可能是相关的。它指的是故障转移，但问题似乎也会影响非群集实例。
Are you using SQL 2012 or higher? If so, this bug report[^] might be relevant. It refers to "failover", but the problem seems to affect non-clustered instances as well.
在SQL Server 2012中，身份属性的实现已更改，以适应对其他功能的投资。在以前版本的SQL Server中，对身份生成的跟踪依赖于生成的每个身份值的事务日志记录。在SQL Server 2012中，我们分批生成标识值，并仅记录批次的最大值。这减少了写入事务日志的信息量和频率，提高了插件的可伸缩性。
In SQL Server 2012 the implementation of the identity property has been changed to accommodate investments into other features. In previous versions of SQL Server the tracking of identity generation relied on transaction log records for each identity value generated. In SQL Server 2012 we generate identity values in batches and log only the max value of the batch. This reduces the amount and frequency of information written to the transaction log improving insert scalability.
Microsoft的解决方案是将跟踪标志272添加到SQL服务器启动选项。这将导致SQL使用与先前版本相同的标识生成语义。但是，它可能会对身份生成性能产生负面影响。
或者，您可以使用创建的序列替换特定的标识列没有CACHE 选项。
数据库引擎服务启动选项 [ ^ ]
创建序列（Transact-SQL） [ ^ ]
The solution from Microsoft is to add trace flag 272 to the SQL Server startup options. This will cause SQL to use the same identity generation semantics as previous versions. However, it may have a negative impact on the identity generation performance.
Alternatively, you can replace specific identity columns with a sequence created with the NO CACHE option.
Database Engine Service Startup Options[^]
CREATE SEQUENCE (Transact-SQL)[^]
这篇关于如何更改记录中的Id字段？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
