为什么向现有列添加可空的默认约束需要这么长时间？

Question

我有一个已存在的表格，大约有4亿行。该表格包括一组名为IsModified、IsDeleted和IsExpired的bit列。

CREATE TABLE [dbo].[ActivityAccumulator](
    [ActivityAccumulator_SK] [int] IDENTITY(1,1) NOT NULL,
    [ActivityAccumulatorPK1] [int] NULL,
    [UserPK1] [int] NULL,
    [Data] [varchar](510) NULL,
    [CoursePK1] [int] NULL,
    [TimeStamp] [datetime] NULL,
    [SessionID] [int] NULL,
    [Status] [varchar](50) NULL,
    [EventType] [varchar](40) NULL,
    [DWCreated] [datetime] NULL,
    [DWModified] [datetime] NULL,
    [IsModified] [bit] NULL,
    [DWDeleted] [datetime] NULL,
    [IsDeleted] [bit] NULL,
    [ActivityAccumulatorKey] [bigint] NULL,
    [ContentPK1] [bigint] NULL
) ON [PRIMARY]

我想为表格添加一个默认约束，对于所有未来插入的行，将这些位列默认为0。我试图通过以下命令实现：

ALTER TABLE ActivityAccumulator 
ADD CONSTRAINT DF_ActivityAccumulatorIsExpired DEFAULT (0) FOR IsExpired

ALTER TABLE ActivityAccumulator 
ADD CONSTRAINT DF_ActivityAccumulatorIsDeleted DEFAULT (0) FOR IsDeleted

ALTER TABLE ActivityAccumulator 
ADD CONSTRAINT DF_ActivityAccumulatorIsModified DEFAULT (0) FOR IsModified

我最终希望回去清理现有数据，将0值放在所有NULL值的位置，但我现在不真正需要这样做。

我只是尝试运行第一个 ADD CONSTRAINT 命令，但已经执行了一个多小时。鉴于我并没有试图更改任何现有值，为什么会花费这么长时间呢？

- AHiggins

Answer 1

可能的一个原因是您的服务器上有另一个进程锁定了这个表。

假设我打开了两个SSMS窗口，在第一个窗口中执行以下命令：

-- Session 1
CREATE TABLE Foo(IsTrue BIT) 
INSERT INTO Foo VALUES (1),(1),(0)
BEGIN TRANSACTION
UPDATE Foo SET IsTrue = 1 - IsTrue

然后保持SSMS窗口处于打开状态，这样事务就不会关闭，尝试在另一个SSMS会话中执行此简单的约束命令将永远挂起：

-- Session 2
ALTER TABLE Foo ADD CONSTRAINT FooDefault DEFAULT(0) FOR IsTrue

请注意，在这个例子中，表的大小或复杂度都是无关紧要的；我必须等待事务完成。在会话2中，我的alter指令不会完成，直到我通过提交事务或关闭会话1来释放Foo上的锁定。

如何确定这是否是您的问题？查看SSMS活动监视器中的“进程”列表。如果您的ALTER指令正在等待其他操作完成，则“阻止者”列中会有一个数字，指示导致问题的命令的会话ID。

该会话可能反过来又在等待另一个会话，依此类推。如果您跟随这些引用，最终会找到一个在“Head Blocker”列中带有1的进程。从那里，您可以决定是否采取适当的措施来终止有问题的进程，或者只是耐心等待它完成。

- Dan

---

是的，对于这样的问题，我的第一个想法也是“死锁”。 - Ellesedil

@Dan，+25似乎不够...谢谢！这正好描述了问题。以前取消的查询仍在KILLED/ROLLBACK状态下运行。我如此专注于所涉及表的大小，以至于从未考虑过检查阻止进程。 - AHiggins

大小并不是完全无关紧要的：如果它是一张小表，那么回滚就不会花费那么长时间。 - Dan

没错。我想现在最好的办法就是等待并观察它运行多长时间...无论如何，我现在知道了我的原始问题的答案。谢谢！ - AHiggins

Answer 2

1. 使用所有约束条件重新创建对象
2. 转储数据
3. 锁定原始对象
4. 切换对象名称

如果您想进行优化、重新索引并避免像Dan提到的冲突，这种方法是最快的。

- ninjabber