列印資訊分批出來

我有簡單的查詢

WHILE ( @counter < 50 )
   BEGIN
       BEGIN TRANSACTION
       PRINT 'Counter = ' + CONVERT(VARCHAR(4), @counter);
       WITH    CTE
                 AS (
                      SELECT TOP 50000 * FROM MyTable
                    )
           DELETE FROM CTE OPTION ( MAXDOP 1 )
       SET @counter += 1
       COMMIT TRAN
   END

當我查看消息時，該PRINT命令不會在每個循環中都返回行，而是顯示為多行。我已經添加了一行，現在它一次顯示了 1 行，整個過程要快得多。

WHILE ( @counter < 50 )
   BEGIN
       WAITFOR DELAY '00:00:00.5' --< This line
       BEGIN TRANSACTION
       PRINT 'Counter = ' + CONVERT(VARCHAR(4), @counter);
       WITH    CTE
                 AS (
                      SELECT TOP 50000 * FROM MyTable
                    )
           DELETE FROM CTE OPTION ( MAXDOP 1 )
       SET @counter += 1
       COMMIT TRAN
   END

那麼為什麼當我為每個循環迭代添加 0.5 秒的等待時間時，它實際上執行得更快，並允許列印消息同時出現？

PRINT輸出被緩衝。如果您替換PRINT ...為，RAISERROR ... WITH NOWAIT您將看到結果。

DECLARE @msg VARCHAR(MAX);
DECLARE @counter INT;
WHILE (@counter < 50)
BEGIN
   BEGIN TRANSACTION
   SET @msg = 'Counter = ' + CONVERT(VARCHAR(4), @counter);
   RAISERROR (@msg, 0, 1) WITH NOWAIT;
   ;WITH CTE
   AS (
       SELECT TOP(50000) * 
       FROM MySchema.MyTable
       )
   DELETE FROM CTE OPTION (MAXDOP 1);
   SET @counter += 1;
   COMMIT TRAN
END

引用自：https://dba.stackexchange.com/questions/65197