sql-server - 在 SQL Server 中更改列的数据类型

Question

我们有一个 SQL Server 2008 数据库，其中的表包含超过 14 亿条记录。由于坐标系的调整，我们不得不将坐标列的数据类型从decimal(18, 2)扩展为decimal(18, 3)。

我们尝试了多种方法，但在执行大约 14 小时后，所有方法都导致异常(事务日志已满)。

这些是我们尝试过的东西:

1. 修改表格

   ALTER TABLE Adress
   ALTER COLUMN Coordinate decimal(18, 3) NULL
   

2. 设计师

   • 取消选中 Tools > Options > Designer > Prevent saving changes that require table re-creation
   • 打开设计器
   • 将列的数据类型更改为decimal(18, 3)
   • 右键单击 > 生成更改脚本...

此脚本的作用是使用新数据类型创建新表，将旧数据复制到新表，删除旧表并重命名新表。

不幸的是，两次尝试都在执行 14 小时后导致事务日志已满异常。

我想，通过 ALTER TABLE... ALTER COLUMN... 更改数据类型只是更改元数据，应该在(毫秒)秒内完成？

• 你知道我可以尝试的任何其他方法吗？
• 为什么我的尝试(尤其是 #1)需要那么多时间？

提前致谢

Answer 1

主要问题似乎是表中保存了大量数据。你的两次尝试似乎都很好。我必须说，由于数据量很大，它们都肯定需要时间。

每次您更改列数据类型时，SQL SERVER 都会尝试将现有数据转换为目标数据类型。处理大量数据的转换可能会导致执行延迟。

此外，我想知道你是否有任何触发器在 table 上。？

嗯!最后，我建议您按照以下步骤操作。至少试一试

1. 删除所有指向旧列的主键/索引/约束，并禁用任何触发器(如果有的话)。
2. 引入具有新数据类型的新 nullable 列(即使它意味着对表不为空)。
3. 现在对表进行更新查询，将新列值设置为旧列值。您可以在更新 1000/100000 批记录时分块更新。您还可以将条件应用于查询以获得更好的结果。
4. 一旦您通过将新列值设置为旧列来更新所有表，然后从设计器中将 NULL 字符删除为 NOT NULL(如果它是 NOT NULL ).
5. 删除/删除旧列。执行选择查询并验证您的更改。

我要补充的最后一点是您的数据库事务日志也已满，可以收缩但要采取一些预防措施。 Here是如何重置事务日志的很好示例。也应该看看这个。

希望对您有所帮助。 :)