SQL Server重构一个列从varchar(50)到uniqueidentifier以及周边问题

Question

我有一个名为message的表格，它在数据库中有近百万行数据。其中有一个externalId列，其类型为varchar(50)。存储在这个列中的值是GUID，但是我想将此列更新为uniqueidentifier。

所以我打算添加一个新的uniqueidentifier列，将所有值复制到这个新的列中，然后删除原始列。然后我将重命名此列为externalId。

我的问题是有数百个存储过程等需要更改，我需要确保不会破坏任何东西。我还必须搜索代码并进行更改，以便我们期望使用GUID而不是字符串（我正在使用C#）。

请问是否有一些提示或建议？

我是否最好只复制此列而不更改现有列，并使任何对其进行选择操作的代码使用GUID列而不是字符串（当前有时超时！）。我还必须更新插入到此表中的任何代码以插入GUID...

我讨厌旧的垃圾代码...

- superlogical

---

op说：“我喜欢传统的垃圾代码...”，而你正在为别人创建自己的传统，以便几年后有人会哭泣... - KM.

是的，但我不使用存储过程。因此，如果我想要重构，我可以在代码中进行，在一个地方，并知道它会起作用。 - superlogical

Answer 1

您可以简单地：

alter table message
    alter column externalId uniqueidentifier

风险在于，如果该列中存储的任何值不是 GUID，则会出现以下错误：

（译者注：这里保留了 HTML 标签）

Conversion failed when converting from a character string to uniqueidentifier.

- Joe Stefanelli

Answer 2

我可能会这样做：

• 复制一份工作文档
• 添加一个数据类型为uniqueidentifier的新列 externalIdGuid
• 尝试将所有externalId转换为新的externalIdGuid

如果运行正常，那么所有的externalId都是有效的GUID - 在这种情况下，您只需将该列转换为uniqueidentifier类型即可：

ALTER TABLE dbo.Message
  ALTER COLUMN externalID uniqueidentifier

- marc_s

Answer 3

原来在那一列上加了一个非聚集索引后，查询速度变得非常快。我只是在Sql Server管理工具中选择了这个查询，然后右键点击并选择“在数据库引擎优化顾问中分析查询”。它告诉我这一列需要一个索引，甚至生成了你需要添加它的SQL脚本 :)

CREATE NONCLUSTERED INDEX [IX_message_external_id] ON [dbo].[message] 
(
    [external_id] ASC
)
INCLUDE ( [message_id],
[message_type_id],
[message_status_id],
[template_id],
[user_id],
[raw_message_body]) WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]

- superlogical

Answer 4

我能给你的唯一真正提示就是一次性完成所有操作。不要分开做，否则会出现问题。

添加列，复制值，删除旧列并重命名新列。然后重新编译所有存储过程。这将给您提供问题区域列表。修复它们并重新编译。如果这部分看起来不错，那么继续进行代码检查。由于是遗留代码，您可能会发现许多其他问题，这些问题您甚至都不知道。

代码将是最棘手的区域。最有可能的问题将涉及运行时错误。制定一个覆盖绝对所有内容的测试计划，并执行它。

- NotMe

---

如何重新编译所有存储过程？ - superlogical

@Jake Scott：只需编写脚本并运行脚本。当SQL Server尝试执行更改语句时，它会抛出任何错误。 - NotMe

@Jake Scott：顺便说一下，你可以轻松地一次性编写所有存储过程的脚本。 - NotMe

Answer 5

我一点也不会这样做。是的，如果一开始就使用了Uniqueidentifier会更好，但除非您有一个无法在不更改它的情况下克服的特定问题，否则您的用户得到了什么价值？

如果您决定这样做。您可以查询sys.procedures。这比使用syscomments或INFORMATION_SCHEMA.ROUTINES要好。

- Conrad Frix

---

我需要加速这段代码，因为它在查找980,000多行中的字符串值时超时了。 - superlogical

Answer 6

检查开发系统是否出现问题的一种非常快速和安全的方法是将表重命名为message_legacy，并创建一个视图称为message，将externalid转换为uniqueidentifier。这不应该影响底层数据，但会给您一个可用的界面，以查看存储过程和其他代码的行为方式。记得授予此视图与表相同的权限，否则您可能会遇到与类型无关而是与权限相关的错误，这正是您想要测试的。

如果您获得了可接受的结果，那么可以更改列定义。个人建议重命名表，创建一个更改了列类型的新表，然后从旧表中插入到新表中，最后删除旧表。

祝好运！

- Sir Wobin