sql - 在 SQL Server 数据库中的非 IDENTITY 类型字段中重新分配 ID

Question

警告:这个不幸的故事包含代码异味和糟糕的设计决策以及技术债务的例子。

如果您熟悉 SOLID 原则，请练习 TDD 并对您的工作进行单元测试，请勿继续阅读 .除非你想对某人的不幸大笑并为自己的出色而幸灾乐祸，因为你知道你永远不会为你的继任者留下如此巨大的废话。

所以，如果你坐得舒服，那么我就开始了。

在我继承并在过去 7 个月中一直支持和修复错误的这个应用程序中，我被一个 6 个半月前离开的开发人员留下了一个令人沮丧的球。是的，我开始两周后。

反正。在这个应用程序中，我们有 clients , employees和 visits表。

还有一个表叫AppNewRef (或类似的东西)......等待它......包含用于其他每个表的下一个记录ID。因此，可能包含以下数据:-

TypeID    Description     NextRef
   1      Employees       804
   2      Clients         1708
   3      Visits          56783

当应用程序为 Employees 创建新行时，它看起来在 AppNewRef表，获取值，将该值用作 ID，然后更新 NextRef柱子。 Clients 也是一样, 和 Visits和所有其他表 NextID要使用的存储在此处。

是的，我知道，没有自动编号 IDENTITY此数据库上的列。一切都以“当它是 Access 应用程序时”为借口。这些 ID 以 long 形式保存在 (VB6) 代码中。因此，最多可能有 20 亿 1.47 亿条记录。好的，这似乎工作得很好。 (除了应用程序正在更新并负责锁定/更新等，而不是数据库)

所以，我们的用户很高兴创建 Employees , Clients , Visits等 Visits ID一次稳定增加几十个。那么问题就来了。我们的客户端在创建批量访问时导致数据库损坏，因为服务器运行良好，应用程序变得无响应。所以他们使用任务管理器杀死应用程序，而不是耐心等待。授予该应用程序似乎锁定。

继续到今年早些时候，开发人员 Tim(真名。这里不保护罪犯)开始修改代码以分阶段进行批量更新，以便 UI 保持“响应”。然后April 来了，他正在处理他的通知(你现在可以想象这个场景，不是吗？)他正在努力完成更新。

4 月底和 5 月初，我们更新了一些客户。在接下来的几个月里，我们更新了越来越多的内容。

Tim(记住真名)和我(在 Tim 离开前两周开始工作)以及一周后开始的另一个新开发人员都没有看到，访问表中的 ID 开始大幅上升。巨大，我的意思是一次 10000、20000、30000。有时几十万。

下面的图表说明了使用的 ID 的快速增加。



十一月滚动。客户调用技术支持并报告他遇到错误。我查看错误消息并询问数据库，以便调试代码。我发现这个值太大了很长时间。我做一些查询，提取信息，将其放入 Excel 并绘制图表。

我不认为让代码处理比 ID 长的任何东西是正确的方法，因为这个应用程序将该 ID 传递到其他 DLL 和 OCX 中，并且破坏这些接口(interface)似乎是我不知道的整个世界不想现在就遇到。

我正在研究的一个潜在想法是尝试修改 ID，以便我可以将它们降低到较低的级别。基本上填补了空白。使用 ROW_NUMBER功能

我正在考虑做的是向每个表添加一个新列，这些表具有对这些访问 ID 的外键引用(不是正确的外键，这些约束不存在于该数据库中)。这个新列可以存储访问 ID 的旧(当前)值(哦，只是为了混淆视听；在某些表上它被称为 EventID ，而在某些表上它被称为 VisitID )。

然后，对于引用该 VisitID 的其他每个表，更新为新值。

想法？建议？ T-SQL 的片段帮助所有人感激地收到。

Answer 1

选项一:

显式约束所有外键关系，并将它们设置为 ON UPDATE CASCADE .

这意味着无论何时更改 ID，外键都会自动更新。

然后你只需运行这样的东西......

WITH
  resequenced AS
(
  SELECT
    ROW_NUMBER() OVER (ORDER BY id) AS newID,
    *
  FROM
    yourTable
)
UPDATE
  resequenced
SET
  id = newID

我在 中没有这样做过年龄 ，所以我忘记了它是否会因具有相同 id 值的两条记录而导致更新中出现问题。如果是这样，你可以先做这样的事情......

UPDATE yourTable SET id = -id

选项二:

确保 无 您的外键关系是明确定义的。如果是，请注意并删除它们。

然后做一些像...

CREATE TABLE temp AS
  newID INT IDENTITY (1,1),
  oldID INT
)

INSERT INTO temp (oldID) SELECT id FROM yourTable

/* Do this once for the table you are re-identifiering              */
/* Repeat this for all fact tables holding that ID as a foreign key */
UPDATE
  factTable
SET
  foreignID = temp.newID
FROM
  temp
WHERE
  foreignID = temp.oldID

然后重新应用任何现有的外键关系。

这是一个非常可怕的选择。如果您忘记更新表，那么您只是在破坏数据。但是，你 可以 给那个 temp表一个更好的名字并保留它。

祝你好运。愿主怜悯你的灵魂。如果你在黑暗的小巷里遇到他，蒂姆就是这样。