參數和執行計畫的重複使用

參數的使用，包括 ADO、OLE DB、和 ODBC 應用程式中的參數標記，可以增加執行計畫的重複使用。

安全性注意事項
使用參數或參數標記來保留使用者輸入的值，會比將值串連到字串中安全，之後會使用資料存取 API 方法、EXECUTE 陳述式或 sp_executesql 預存程序來執行該字串。

下列這兩個 SELECT 陳述式的唯一差異，在於 WHERE 子句中所比較的值：

SELECT * FROM AdventureWorks.Production.Product WHERE ProductSubcategoryID = 1;

SELECT * FROM AdventureWorks.Production.Product WHERE ProductSubcategoryID = 4;

這些查詢執行計畫間的唯一差異，是用來比較 ProductSubcategoryID 資料行所儲存的值。雖然目標是要讓 SQL Server 能一直意識到，陳述式基本上所產生的都是相同的計畫，並且重複使用這些計畫，但有時 SQL Server 並不會在複雜的 SQL 陳述式中偵測到這種情況。

利用參數將 SQL 陳述式中的常數分離出來，可以幫助關聯式引擎識別重複的計畫。您可以使用以下方式來使用參數：

• 在 Transact-SQL，使用 sp_executesql：

  DECLARE @MyIntParm INT
  SET @MyIntParm = 1
  EXEC sp_executesql
    N'SELECT * FROM AdventureWorks.Production.Product WHERE ProductSubcategoryID = @Parm',
    N'@Parm INT',
    @MyIntParm
  

  這個方式適用於 Transact-SQL 指令碼、預存程序或動態產生 SQL 陳述式的觸發程序。

• ADO、OLE DB、和 ODBC 使用參數標記。參數標記是取代 SQL 陳述式中常數的問號 (?)，這些標記將繫結至程式變數。例如，您可以在 ODBC 應用程式中執行下列動作：

  • 使用 SQLBindParameter 將整數變數繫結到 SQL 陳述式中的第一個參數標記。

  • 在變數中放入整數值。

  • 執行陳述式，指定參數標記 (?)：

    SQLExecDirect(hstmt, 
      "SELECT * FROM AdventureWorks.Production.Product WHERE ProductSubcategoryID = ?",
      SQL_NTS);
    

    當應用程式中使用參數標記時，SQL Server 所包含的 SQL Server Native Client OLE DB 提供者和 SQL Server Native Client ODBC 驅動程式，會使用 sp_executesql 將陳述式傳送至 SQL Server。

• 設計預存程序，按設計來使用參數。

如果您未將參數明確建置到應用程式的設計中，也可以仰賴 SQL Server 查詢最佳化工具，利用簡單參數化的預設行為將特定查詢自動參數化。另外，您也可以強制查詢最佳化工具來考慮將資料庫中的所有查詢參數化，其方式是將 ALTER DATABASE 陳述式的 PARAMETERIZATION 選項設為 FORCED。如需詳細資訊，請參閱＜強制參數化＞。

啟用「強制參數化」之後，仍會發生「簡單參數化」。例如，根據強制參數化的規則，下列查詢無法參數化：

SELECT * FROM Person.Address
WHERE AddressID = 1 + 2;

不過，可根據簡單參數化規則將它參數化。如果強制參數化嘗試失敗，後續仍會嘗試簡單參數化。

請參閱

參考

sp_executesql (Transact-SQL)

SQL Server 的 SQL Statistics 物件

概念

命令參數

使用陳述式參數