更改表中的 2 列 - 有风险的操作吗？
我在 MSSQL Server 2005 上有一个包含约 100 列、约 30M 行的表。
我需要更改 2 列 - 将它们的类型从 VARCHAR(1024) 更改为 VARCHAR(max)。这些列没有索引。
我担心这样做会填满日志，并导致操作失败。如何估计此类操作所需的可用磁盘空间（数据和日志）以确保其不会失败？
I have a table with ~100 columns, about ~30M rows, on MSSQL server 2005.
I need to alter 2 columns - change their types from VARCHAR(1024) to VARCHAR(max). These columns does not have index on them.
I'm worried that doing so will fill up the log, and cause the operation to fail. How can I estimate the needed free disk space, both of the data and the log, needed for such operation to ensure it will not fail?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
你是对的，增加列大小（包括到 MAX）将为大型表生成巨大的日志，因为每一行都将被更新（在幕后旧列被删除，新列被添加并复制数据）。
VARCHAR(MAX) NULL 类型的新列。作为可为空的列，将仅添加为元数据（无数据更新）sp_rename将新列重命名为旧的列名称。这样您就可以通过在步骤 2）中控制批次来控制日志。您还可以通过不将整个表复制到新表中来最大程度地减少对权限、约束和关系的破坏（SSMS 做得很差......）。
您可以同时对两列执行此顺序。
You are right, increasing the column size (including to MAX) will generate a huge log for a large table, because every row will be updated (behind the scenens the old column gets dropped and a new column gets added and data is copied).
VARCHAR(MAX) NULL. As a nullable column, will be added as metadata only (no data update)sp_renameto rename the new column to the old column name.This way you get control over the log by controlling the batches at step 2). You also minimize the disruption on permissions, constraints and relations by not copying the entire table into a new one (as SSMS so poorly does...).
You can do this sequence for both columns at once.
我建议您考虑：
这可能是一个成本低得多的操作，可以通过以下方式完成使用 INSERT/SELECT 进行最少的日志记录（如果是 SQL Server 2008 或更高版本）。
I would recommend that you consider, instead:
This might be a far less costly operation and could possibly be done with minimal logging using INSERT/SELECT (if this were SQL Server 2008 or higher).
为什么增加 VARCHAR 限制会填满日志？
Why would increasing the VARCHAR limit fill up the log?
尝试以较小的片段进行一些测试。我的意思是，您可以在本地创建具有几千行的相同结构，并查看前后的差异。我认为变化将是线性的。真正的问题是关于重做日志，它是否适合它，因为您可以立即完成它。必须在网上做，还是可以暂时停止生产？如果你可以停止，也许有一种方法可以像 Oracle 一样停止 MSSQL 中的重做日志。它可以让它变得更快。如果你需要在线完成，你可以尝试创建一个新列，将值循环复制到其中，例如一次复制100000行，提交，继续。完成后，也许删除原始列并重命名新列比更改更快。
Try to do some test in smaller pieces. I mean, you could create the same structure locally with few thousand rows, and see the difference before and after. I think the change will be linear. The real question is about redo log, if it will fit into it or not, since you can do it at once. Must you do it online, or you can stop production for a while? If you can stop, maybe there is a way to stop redo log in MSSQL like in Oracle. It could make it a lot faster. If you need to do it online, you could try to make a new column, copy the value into it by a cycle for example 100000 rows at once, commit, continue. After completing maybe to drop original column and rename new one is faster than altering.