[SQL] WHERE 中的動態篩選條件

T-SQL 撰寫中看見動態篩選的 WHERE 條件，是利用 COALESCE()、ISNULL()、IIF() 或 CASE WHEN 來作到，主要是為了減少 T-SQL 的撰寫或是要避免根據使用者輸入條件來串出 T-SQL，此作法可以獲得相同結果，但最大影響在於效能。

• 利用 AdventureWorks2012 的 Sales.SalesOrderHeader 和 Sales.SalesOrderDetail 來說明看見的情況

CREATE PROCEDURE uspDemo
(
  @訂單時間 AS datetime , 
  @顧客編號 AS int ,
  @產品編號 AS int
)
AS
  BEGIN

    SELECT
      OH.SalesOrderID ,
      OH.CustomerID ,
      OD.ProductID
    FROM [Sales].[SalesOrderHeader] AS OH
      JOIN [Sales].[SalesOrderDetail] AS OD ON OH.SalesOrderID = OD.SalesOrderID
    WHERE OH.OrderDate = ISNULL(@訂單時間 , OH.OrderDate)
      AND OH.CustomerID = ISNULL(@顧客編號 , OH.CustomerID) -- 動態篩選條件
      AND OD.ProductID = ISNULL(@產品編號 , OD.ProductID)

  END

動態篩選條件是利用 ISNULL() 來判斷 @顧客編號 的值，值為 NULL，WHERE 條件就會變成 OH.CustomerID = OH.CustomerID 也就是 1 = 1 的條件，永遠成立，此篩選條件就沒有作用，利用此特性 Store Procedure 就可以根據使用者輸入的條件，來控制 WHERE 的篩選條件。

• 利用 AdventureWorks2012 的 Person.Contact 來觀察效能差異

建立 uspNormal 和 uspDynamic 這兩個 SP，內容都是要找出 ContactID 是哪位，uspNormal 是正常 WHERE 條件、uspDynamic 則是動態 WHERE 條件

IF OBJECT_ID('uspNormal') IS NOT NULL
  DROP PROC uspNormal

IF OBJECT_ID('uspDynamic') IS NOT NULL
  DROP PROC uspDynamic
GO 

CREATE PROCEDURE uspNormal
(
  @ContacID int
)
AS
  BEGIN

    SELECT ContactID , FirstName , MiddleName , LastName
    FROM [Person].[Contact]
    WHERE ContactID = @ContacID

  END
GO

CREATE PROCEDURE uspDynamic
(
  @ContacID int
)
AS
  BEGIN

    SELECT ContactID , FirstName , MiddleName , LastName
    FROM [Person].[Contact]
    WHERE ContactID = ISNULL(@ContacID,ContactID) 
                                                    
  END
GO

執行 uspNormal 和 uspDynamic，要找到 ContacID 為 10 的資料

-- Ctrl + M 開啟執行計畫
SET NOCOUNT ON       -- 避免回傳影響資料筆數
SET STATISTICS IO ON -- 開啟觀察 Logical Read

EXEC uspNormal 10
EXEC uspDynamic 10

• 一樣的結果

• uspNormal Logical Read 為 2、uspDynamic Logical Read 為 569

• 從執行計畫中可以看到 uspNormal 利用 Index Seek、uspDynamic 利用 Index Scan 來篩選資料。

上述的簡單範例，就可以看出 WHERE 中的動態篩選條件會導致 Query Optimizer 無法正確地利用 Index 來搜尋資料，建議不要在 WHERE 中使用動態篩選條件，以免導致效能低落。

參考資料
• SQL筆記：再談動態WHERE條件