使用默认值修改包含数百万行的表

Question

我正在尝试运行如下的alter命令，但是这个表有4000万行，查询需要很长时间。

ALTER TABLE [dbo].[ConsumerProduct]
ADD IsPendDefault [bit] NOT NULL DEFAULT ((0))
GO

因此，我想到用null做alter，而不是更新4000万行的每列，每批5000行，然后把它修改回非null。

像这样

ALTER TABLE [dbo].ConsumerProduct
ADD IsPendDefault [bit] NULL
GO
SET ROWCOUNT 10000
WHILE (1=1) 
BEGIN
    BEGIN TRANSACTION
    UPDATE ConsumerProduct 
    SET IsPendDefault = 0 
    WHERE IsPendDefault IS NULL
    -- Update 1000 nonupdated rows
    IF @@ROWCOUNT = 0
    BEGIN
        COMMIT TRANSACTION
        BREAK
    END
    COMMIT TRANSACTION
END
ALTER TABLE [dbo].ConsumerProduct
ALTER COLUMN  IsPendDefault [bit] NOT NULL 
GO

即使这个查询也要花很长时间。

是否有更简单、更快速的方法来修改列中具有默认值的表?我使用的是SQL Server 2012.

Answer 1

SQL Server 2012企业版

ALTER TABLE [dbo].[ConsumerProduct]
ADD IsPendDefault [bit] NOT NULL DEFAULT ((0))

是一个在线操作。所以我猜你不在EE。

您真的需要将所有列值更新为0吗?

一个选项是允许列为空，默认为NULL

ALTER TABLE [dbo].ConsumerProduct
ADD IsPendDefault [bit] NULL

并确保您的代码将NULL视为false。例如:与SELECT ISNULL(IsPendDefault,0) AS IsPendDefault .

如果你想让它成为NOT NULL，那么分批执行更新是最好的解决方案。但是，您希望确保每个更新都能快速找到要更新的那批行，而不必扫描以前批次已经更新过的行。

所以(如果你有一个整数标识主键)，一个选择就是简单地把它分成你想要的批大小的范围，并让每个批查找到它想要的范围。

DECLARE @I         INT = 0,
        @BatchSize INT = 5000;
WHILE @I <= (SELECT MAX(ID)
             FROM   ConsumerProduct)
  BEGIN
      UPDATE ConsumerProduct
      SET    IsPendDefault = 0
      WHERE  IsPendDefault IS NULL
             AND ID >= @I
             AND ID < @I + @BatchSize;
      SET @I = @I + @BatchSize;
  END

如果您有大范围的空区域或填充极其稀疏的区域，则可能值得使用更详细的方法。

CREATE TABLE #processed
  (
     ID INT PRIMARY KEY
  )
DECLARE @ID        INT = 0,
        @BatchSize INT = 5000;
WHILE 1 = 1
  BEGIN
      WITH T
           AS (SELECT TOP (@BatchSize) *
               FROM   ConsumerProduct
               WHERE  ID > @ID
                      AND IsPendDefault IS NULL
               ORDER  BY ID)
      UPDATE T
      SET    IsPendDefault = 0
      OUTPUT inserted.ID
      INTO #processed;
      IF @@ROWCOUNT < @BatchSize
        BREAK;
      SELECT @ID = MAX(ID)
      FROM   #processed;
      TRUNCATE TABLE #processed
  END
DROP TABLE #processed