萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MS SQL 資料庫

sp_executesql vs EXEC:參數化動態 SQL、執行計畫重用與效能差異深度解析

動態 SQL 執行有兩大入口:EXEC (@sql) 與 sp_executesql。後者支援參數化,能大幅提升計畫重用率與效能,是現代 T-SQL 開發首選。

語法對比

-- EXEC:字串拼接,無參數化
DECLARE @sql NVARCHAR(MAX) = N'SELECT COUNT(*) FROM Emp WHERE Id = ' + @Id;
EXEC (@sql);  -- 每次 @Id 不同 → 不同 SQL 文本 → 重新編譯

-- sp_executesql:參數化
DECLARE @sql NVARCHAR(MAX) = N'SELECT @Cnt = COUNT(*) FROM Emp WHERE Id = @Id';
DECLARE @Cnt INT, @Id VARCHAR(20) = '1';
EXEC sp_executesql @sql,
    N'@Cnt INT OUTPUT, @Id VARCHAR(20)',
    @Cnt OUTPUT, @Id;
PRINT @Cnt;

核心差異

維度EXEC (@sql)sp_executesql
參數化不支援(需字串拼接)支援輸入/輸出參數
計畫重用低(每組參數值產生新計畫)高(相同參數化 SQL 共用計畫)
編譯成本重複編譯首次編譯後重用
注入風險高(拼接易注入)低(參數強型別)
型別要求VARCHAR/NVARCHAR參數清單與 SQL 必須為 NVARCHAR
回傳結果集直接回傳直接回傳 + OUTPUT 參數

sp_executesql 參數綁定規則

  1. 第 2 參數(參數定義)必須為 N'...'(Unicode 字串常值),格式:N'@p1 TYPE1 [OUTPUT], @p2 TYPE2 ...'。
  2. 第 3+ 參數:順序對應或命名對應(@p1=@v1, @p2=@v2)。
  3. OUTPUT 參數需在定義與呼叫雙端都標記 OUTPUT。

效能實測情境

高頻呼叫(每秒數百次)的參數化查詢:

  • EXEC:Plan Cache 暴增(數萬單用計畫),CPU 高、記憶體壓力大。
  • sp_executesql:單一參數化計畫重用,Plan Cache 穩定,CPU 降 70%+。

注意事項

  • SQL Server 2005 SP3+ 強制參數定義與 SQL 字串為 NVARCHAR,VARCHAR 會報錯。
  • 動態 SQL 內部無法存取外部暫存表 (#temp),需用全域暫存表 (##temp) 或表變數/傳遞 TVP。
  • 過度參數化可能導致參數嗅探問題,必要時加 OPTION (RECOMPILE) 或 OPTIMIZE FOR。

最佳實務

  1. 所有動態 SQL 預設用 sp_executesql。
  2. 僅在極簡單、極低頻、無參數的 DDL/DML 用 EXEC。
  3. 參數定義常數化(存於變數/常數表),避免重複字串字面值佔用 Plan Cache。

參數化動態 SQL 是 SQL Server 高效能、安全、可維護程式碼的基石。

copyright © 萬盛學電腦網 all rights reserved