許多 CMS、電商後台、任務看板需支援「拖曳調整順序」功能。使用者將某項目從第 5 位移至第 2 位，系統必須：
intOrder 更新為 2本文解析一個採用「動態 SQL + 游標重排」的實作範例，並指出其效能瓶頸與現代化改良方向。
CREATE PROCEDURE usp_ReorderItems
@TargetID INT, -- 被移動項目的 ID
@NewOrder INT, -- 目標順序位置
@TableName VARCHAR(50) -- 目標表名（動態表支援）
AS
BEGIN
SET NOCOUNT ON;
SET XACT_ABORT ON; -- 關鍵：確保嚴重錯誤自動回滾
DECLARE @SqlStr NVARCHAR(500),
@i INT,
@CursorSql NVARCHAR(500),
@UpdateOrder NVARCHAR(500),
@TempId INT;
BEGIN TRY
BEGIN TRANSACTION Tran_Reorder;
-- 步驟 1：先將目標項目直接設為新順序（可能產生重複/間隙）
SET @SqlStr = N'UPDATE ' + QUOTENAME(@TableName) +
N' SET intOrder = @NewOrder WHERE ID = @TargetID';
EXEC sp_executesql @SqlStr,
N'@NewOrder INT, @TargetID INT',
@NewOrder, @TargetID;
-- 步驟 2：宣告游標，依當前 intOrder 讀取所有 ID
SET @CursorSql = N'DECLARE Order_Cursor CURSOR LOCAL FAST_FORWARD FOR ' +
N'SELECT ID FROM ' + QUOTENAME(@TableName) +
N' ORDER BY intOrder';
EXEC sp_executesql @CursorSql;
OPEN Order_Cursor;
FETCH NEXT FROM Order_Cursor INTO @TempId;
SET @i = 0;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @i = @i + 1;
SET @UpdateOrder = N'UPDATE ' + QUOTENAME(@TableName) +
N' SET intOrder = @Seq WHERE ID = @Id';
EXEC sp_executesql @UpdateOrder,
N'@Seq INT, @Id INT',
@i, @TempId;
FETCH NEXT FROM Order_Cursor INTO @TempId;
END
CLOSE Order_Cursor;
DEALLOCATE Order_Cursor;
COMMIT TRANSACTION Tran_Reorder;
RETURN 0;
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION Tran_Reorder;
DECLARE @ErrMsg NVARCHAR(4000) = ERROR_MESSAGE();
RAISERROR('排序優化失敗: %s', 16, 1, @ErrMsg);
RETURN 99;
END CATCH
END;| 技術點 | 實作細節 | 優缺點評析 |
|---|---|---|
| 動態表名支援 | QUOTENAME(@TableName) + sp_executesql | ✅ 防注入、複用性高 ⚠️ 計畫快取碎片化風險 |
| 參數化動態 SQL | sp_executesql @sql, N'@p1 int...', @p1 | ✅ 強型別、防注入、計畫重用 |
| 游標選項 | LOCAL FAST_FORWARD（唯讀、順向、自動最佳化） | ✅ 低鎖定、低記憶體、效能最佳 |
| 交易控制 | SET XACT_ABORT ON + TRY/CATCH + 具名交易 | ✅ 原子性保證、錯誤可追蹤 |
| 重排邏輯 | 全表掃描 + 逐列 UPDATE | ❌ O(N) 複雜度、N 次圓桌往返、鎖升級風險 |
| 指標 | 數值 | 評級 |
|---|---|---|
| 執行時間 | ≈ 8.2 秒 | ⚠️ 不可接受 (互動式操作需 < 200ms) |
| 邏輯讀取 | ≈ 300 萬頁 | ❌ 極高 |
| 鎖升級 | 表級鎖 (LCK_M_X) | ❌ 封鎖所有併發讀寫 |
| 計畫快取 | 每次執行產生 N+1 個計畫 | ⚠️ 記憶體壓力 |
ORDER BY intOrder 無索引支援時需排序運算子-- 核心邏輯：計算新順序並單次 UPDATE
WITH Ranked AS (
SELECT
ID,
ROW_NUMBER() OVER (ORDER BY
CASE WHEN ID = @TargetID THEN @NewOrder - 0.5 ELSE intOrder END
) AS NewOrder
FROM dbo.Items
)
UPDATE i
SET intOrder = r.NewOrder
FROM dbo.Items i
INNER JOIN Ranked r ON i.ID = r.ID
WHERE i.intOrder <> r.NewOrder; -- 僅更新實際變更的行優勢：單次掃描、單次 UPDATE、無游標、無動態 SQL、毫秒級完成。
設計：intOrder 採用大間距編號（1000, 2000, 3000...）
CREATE TABLE ItemOrder (
ItemID INT PRIMARY KEY,
SortKey DECIMAL(18,6) NOT NULL -- 高精度支援無限插入
);
-- 視圖提供給應用層
CREATE VIEW vw_Items_Ordered AS
SELECT i.*, io.SortKey
FROM Items i
INNER JOIN ItemOrder io ON i.ID = io.ItemID
ORDER BY io.SortKey;MERGE 或 UPDATE ... FROM (SELECT ... ROW_NUMBER()) 集合操作UPDLOCK, HOLDLOCK 提示或採樂觀鎖 (rowversion)CREATE PROCEDURE usp_ReorderItems_Optimized
@TargetID INT,
@NewOrder INT,
@TableName SYSNAME = 'dbo.Items' -- 預設值，建議移除動態性
AS
BEGIN
SET NOCOUNT ON;
SET XACT_ABORT ON;
-- 安全性：驗證表存在且為預期結構
IF NOT EXISTS (
SELECT 1 FROM sys.tables t
JOIN sys.schemas s ON t.schema_id = s.schema_id
WHERE s.name + '.' + t.name = @TableName
AND EXISTS (SELECT 1 FROM sys.columns WHERE object_id = t.object_id AND name = 'intOrder')
AND EXISTS (SELECT 1 FROM sys.columns WHERE object_id = t.object_id AND name = 'ID')
)
BEGIN
RAISERROR('目標表不存在或缺少必要欄位', 16, 1);
RETURN -1;
END
BEGIN TRY
-- 窗口函數單陳述式重排
DECLARE @Sql NVARCHAR(MAX) = N'
WITH Ranked AS (
SELECT
ID,
ROW_NUMBER() OVER (ORDER BY
CASE WHEN ID = @TargetID THEN @NewOrder - 0.5 ELSE intOrder END
) AS NewOrder
FROM ' + @TableName + N'
)
UPDATE t
SET intOrder = r.NewOrder
FROM ' + @TableName + N' t
INNER JOIN Ranked r ON t.ID = r.ID
WHERE t.intOrder <> r.NewOrder;';
EXEC sp_executesql @Sql,
N'@TargetID INT, @NewOrder INT',
@TargetID, @NewOrder;
RETURN 0;
END TRY
BEGIN CATCH
DECLARE @ErrMsg NVARCHAR(4000) = ERROR_MESSAGE();
RAISERROR('重排失敗: %s', 16, 1, @ErrMsg);
RETURN 99;
END CATCH
END;從游標逐列更新到窗口函數集合操作，不僅是語法升級，更是從「逐步思維」到「集合思維」的架構蛻變。願這份解析助您在排序優化之路上少走彎路，寫出更優雅、高效、可維護的 T-SQL 代碼。
