sql-server - “n个受影响的行”在SQL Server中如何工作？

DECLARE @EndID BIGINT,
        @StartID BIGINT,
        @n_batchSize INT = 3000

SET @EndID = (SELECT MAX(ID) FROM Table WHERE NewColumn IS NULL)

WHILE (@EndID>0)
BEGIN
      SET @StartID = @EndID - @n_batchSize;

      UPDATE Table WITH (ROWLOCK)
      SET NewColumn =
        (CASE
            WHEN (ColumnA IS NOT NULL AND ColumnA > 0) THEN ColumnA
            ELSE
            (
                SELECT  TableC.ID
                FROM TableB AS B WITH(NOLOCK)
                INNER JOIN TableC AS C WITH(NOLOCK)
                    ON B.ID = C.ID
                WHERE  C.ID = Table.ID
            ) END
        )
      WHERE ID BETWEEN @StartID AND @EndID
      AND NewColumn IS NULL

      SET @EndID = @EndID - @n_batchSize;

      WAITFOR DELAY '00:00:05'
END

执行以上脚本以执行数据修补操作。

等待完成后，NewColumn的某些值保持为空。

NewColumn IS NULL的计数为140，并且第二次执行相同的脚本。完成后，几乎没有“ n行受影响”的批次，如下所示：



当我检查NewColumn IS NULL的计数时，它仍然是140。因此，我最好的猜测是“受影响的n行”是由于SET部分中的选择查询所致。

为了进行实验，我专门针对一条记录运行，并查看它如何与以下查询一起使用

UPDATE Table WITH (ROWLOCK)
SET NewColumn =
    (CASE
        WHEN (ColumnA IS NOT NULL AND ColumnA > 0) THEN ColumnA
        ELSE
        (
            SELECT  TableC.ID
            FROM TableB AS B WITH(NOLOCK)
            INNER JOIN TableC AS C WITH(NOLOCK)
                ON terminal.LocationID = location.LocationID
            WHERE  C.ID = Table.ID
        ) END
    )
WHERE ID = 1 AND EntryZoneID IS NULL

结果如下：



根据结果​​，看来我的猜测是错误的。该语句不是因为子选择查询？

最佳答案

受影响的n行将来自您的更新查询。它显示了更新了多少行。这不是因为子查询。这是因为您的更新查询正在根据您的where条件更新行

 WHERE ID BETWEEN @StartID AND @EndID
  AND NewColumn IS NULL