游標逐行處理效能差、鎖持久、程式碼冗長。SQL Server 2005+ 引入 ROW_NUMBER() 窗口函數,配合 WHILE 迴圈可實現「偽集合」逐行邏輯,效能大幅提升。
-- 1. 將目標資料集物化至暫時表,並產生連續行號
SELECT Department_No AS DepartmentNo,
ROW_NUMBER() OVER (ORDER BY Department_No) AS RowNum
INTO #DeptTemp
FROM Departments;
-- 2. 取得最大行號
DECLARE @MaxRow INT = (SELECT MAX(RowNum) FROM #DeptTemp);
DECLARE @CurrentRow INT = 1;
-- 3. WHILE 迴圈逐行處理
WHILE @CurrentRow <= @MaxRow
BEGIN
-- 取得當前行資料
DECLARE @DeptNo NVARCHAR(50);
SELECT @DeptNo = DepartmentNo FROM #DeptTemp WHERE RowNum = @CurrentRow;
-- ===== 此處放置單行業務邏輯 =====
-- 例:呼叫存儲過程、動態 SQL、跨資料庫操作等無法集合化的動作
-- EXEC usp_ProcessDepartment @DeptNo;
PRINT 'Processing: ' + @DeptNo;
-- ================================
SET @CurrentRow += 1;
END
-- 4. 清理
DROP TABLE #DeptTemp;
#Temp 擁有統計資訊,優化器可產生更好計畫;@Table 無統計資訊,大資料量易導致嚴重誤估。OVER (ORDER BY ...) 保證每次執行行號一致,避免並行計畫導致順序不穩。EXEC usp_XXX @Param,保持主邏輯清晰,便於單元測試。COMMIT 一次,避免長交易鎖升級與日誌暴增。若單行邏輯無相依關係,可改用 sp_execute_external_script (R/Python) 或 SSIS 並行任務,或將迴圈拆分為多批次併發執行(需應用層協調)。
-- ❌ 反模式:WHILE 迴圈內直接 SELECT/UPDATE 永久表
WHILE @i <= @max
BEGIN
UPDATE BigTable SET Col = @i WHERE Id = @i; -- 每次獨立查詢計畫、鎖、網路往返
SET @i += 1;
END
此模式將集合操作退化為 RBAR (Row By Agonizing Row),效能災難。務必先物化至暫時表。