logo标签列表

SQL Server ALTER字段NOT NULL需要很长时间

sqlsql-servernullableblockingddl
12

我想更改一个有大约400万记录的表中的字段。我确保所有这些字段值都不是NULL，并希望将此字段更改为NOT NULL。

ALTER TABLE dbo.MyTable
ALTER COLUMN myColumn int NOT NULL

更新这个似乎需要很长时间。是否有加速的方法或者只能在非工作时间隔夜进行？

这样做会引起表锁吗？

1
个人而言，我绝不会在任何时候（除了非高峰期）对现有大型表的表结构进行更改。即使这很快，也可能会导致正在执行任务的用户在你进行更改时遇到问题。任何重大更改最好都在单用户模式下完成。最好安排一个黑屏计划维护期间，当用户无法执行任何操作（当然要事先通知且在非高峰期），而不是让正在做某事的不满用户出现错误。 - HLGEM
你要修改的列是否涉及到外键约束？ - onupdatecascade
1
从分析器的快速测试中可以看出，它在表上获取了一个Sch-M锁基本上与所有操作都不兼容。然后，它必须读取每个页面以确定所有行是否有效。 - Martin Smith
更改前该列是整数类型吗？ - gbn
3个回答
4
您可以更改字段并使其不为null，而不必检查字段。如果您真正担心在非工作时间进行此操作，可以向该字段添加约束条件，以确保它不为null。这将允许您使用“无需检查”选项，而不必检查每个400万行以查看是否更新。
CREATE TABLE Test
(
    T0 INT Not NULL,
    T1 INT NUll 
)

INSERT INTO Test VALUES(1, NULL) -- Works!

ALTER TABLE Test
    WITH NOCHECK
        ADD CONSTRAINT N_null_test CHECK (T1 IS NOT NULL)

    ALTER COLUMN T1 int NOT NULL 

INSERT INTO Test VALUES(1, NULL) -- Doesn't work now!

实际上，你有两个选择（添加了第三个选项，请参见编辑）：

  1. 使用约束条件来防止更新任何新行并保留原始行不变。
  2. 将空值行更新为其他值，然后应用非空修改选项。除非你不介意进程被锁定在表中，否则这应该在非工作时间运行。

根据你的具体情况，任何一个选项都可能更好。我不会选择第二个选项，因为你必须在非工作时间运行它。从长远来看，你在半夜更新的时间将花费得很值，而相比节省几个小时可能面临的头疼问题，这是一种捷径。

话虽如此，如果你要选择第二个选项，你可以最小化非工作时间所需的工作量。由于你必须确保在修改列之前将行更新为非空，所以你可以编写一个游标来慢慢地（相对于一次性完成）进行更新。

  1. 遍历每一行
  2. 检查是否为空
  3. 适当地更新它。 这将花费一些时间，但不会锁定整个表格，阻止其他程序访问它。(不要忘记使用with(rowlock)表提示！)
编辑：我刚想到第三种选择： 您可以创建一个具有适当列的新表，然后将原始表中的数据导出到新表中。完成后，您可以删除原始表并将新表的名称更改为旧表的名称。要做到这一点，您必须在原始表上禁用依赖关系，并在完成后在新表上重新设置它们，但是此过程将大大减少您在非工作时间所需进行的工作量。这与在管理工作室通过 SQL Server 对表进行列排序更改时使用的方法相同。对于此方法，我会分批插入以确保不会对系统造成不必要的压力并阻止其他人访问它。然后在非工作时间，您可以删除原始表，重命名第二个表，并应用依赖关系等。您仍然需要一些非工作时间的工作，但与其他方法相比，这将是微不足道的。

使用sp_rename的链接。

4
如果使用NO CHECK，则该约束将不被信任，无法被查询优化器使用。请参阅http://sqlblog.com/blogs/tibor_karaszi/archive/2008/01/12/non-trusted-constraints-and-performance.aspx。 - Shannon Severance
3
“NOCHECK” 关键字不适用于 NULL/NOT NULL。它仅适用于 CONSTRAINT 子句中的约束条件。 - Tom H
从问题中，OP已经确保他们的列不包含任何NULL值，所以不确定您答案的第二部分的相关性？ - Martin Smith
@martin，他可能已确保它，但是下一个阅读答案的人可能不知道。为了覆盖另一种情况，这只需要很少的空间。如果 OP 犯了错误并拥有 Null 值，那么他就有更多信息来做出决定。 - kemiller2002
4
我所知道的唯一快速完成此操作的方法是：
1. 创建一个“影子”表，该表具有所需的布局。 2. 向源表添加触发器，以便将任何插入/更新/删除操作复制到影子表中（注意捕获任何可能出现的NULL值！）。 3. 将所有数据从源表复制到影子表中，可能分成小块（确保您可以通过触发器处理已复制的数据，确保数据适合新结构（ISNULL（？）！）。 4. 从其他表中脚本化所有依赖项。 5. 当所有工作完成后，在显式事务内执行以下操作： - 在源表上获取独占表锁和影子表上的表锁。 - 运行脚本以删除对源表的依赖关系。 - 将源表重命名为其他名称（例如后缀_old）。 - 将影子表重命名为源表的原始名称。 - 运行脚本以重新创建所有依赖项。 6. 您可能希望在事务之外执行最后一步，因为它可能需要相当长的时间，具体取决于引用此表的表的数量和大小，前面的步骤不需要花费太多时间。
请注意：不要试图使用NOCHECK重新创建FK，因为这会使它们变得无效，优化器不会信任它们，也不会在构建查询计划时考虑它们。
（*：快速的含义是：最短的停机时间）
我过去一周一直在琢磨这个想法，但在我们特定的情况下，我们发现存在问题，因为可能会有多个列更改彼此不知道，因此动态创建“代替”触发器是不可能的（这一切都要自动化）。今天早上我想到它可以是表驱动的，我打算在这里发布答案，只是发现你比我先到了 :) - Tom H
2

抱歉让您失望，但：

  • 有什么方法可以加快速度吗：没有，除非您想要改变表结构本身
  • 还是我只能在非工作时间的过夜期间进行操作吗？是的，这可能是最好的选择，正如@HLGEM所指出的那样
  • 这也可能导致表锁定吗？是的

虽然与您无直接关系（因为它涉及从NOT NULL到NULL的转换），但这是一个有趣的阅读话题：http://beyondrelational.com/blogs/sankarreddy/archive/2011/04/05/is-alter-table-alter-column-not-null-to-null-always-expensive.aspx

最后提供一些古老的历史 - 在2005年论坛上的一个类似问题中，与@Kevin提供的建议相同 - 使用约束而不是将列本身设置为非空：http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=50671

网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接