sql-server - "n rows affected"在 SQL Server 中如何工作？

Question

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部分值仍然为null。

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

结果如下:

从结果来看，我的猜测似乎是错误的。语句是不是因为sub-select-query？

Answer 1

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

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