[SQL] 篩選索引和參數化查詢

把 AdventureWorks2017 的 Sales.SalesOrderHeader.PurchaseOrderNumber 欄位當成測試目標，來模擬 TSQL 參數化，對於篩選索引使用的影響

建立篩選索引

要針對欄位有值資料建立篩選索引，排除 NULL 和空值

USE [AdventureWorks2017]
GO

DROP INDEX IF EXISTS IF_SalesOrderHeader_PurchaseOrderNumber ON Sales.SalesOrderHeader

CREATE INDEX IF_SalesOrderHeader_PurchaseOrderNumber ON Sales.SalesOrderHeader (PurchaseOrderNumber)
WHERE PurchaseOrderNumber IS NOT NULL
   AND PurchaseOrderNumber <> ''

測試使用篩選索引來搜尋資料

從執行計畫中可以觀察到有用到篩選索引

SELECT PurchaseOrderNumber
FROM Sales.SalesOrderHeader
WHERE PurchaseOrderNumber = N'PO18850127500'

TSQL 參數化

從執行計化可以觀察到，參數化後 Query Optimizer 無法判斷是否使用篩選索引，跑回叢集索引去

DECLARE @TSQL nvarchar(max)
DECLARE @PurchaseOrderNumber nvarchar(25) = N'PO18850127500'

SET @TSQL = 
    'SELECT 
       PurchaseOrderNumber
     FROM Sales.SalesOrderHeader
     WHERE PurchaseOrderNumber = @PurchaseOrderNumber'

EXEC dbo.sp_executesql 
    @TSQL,
    N'@PurchaseOrderNumber nvarchar(25)',
    @PurchaseOrderNumber

TSQL 參數化改善

WHERE 內加入條件，讓 Query Optimizer 有評估篩選索引依據，就會使用到篩選索引

DECLARE @TSQL nvarchar(max)
DECLARE @PurchaseOrderNumber nvarchar(25) = N'PO18850127500'

SET @TSQL = 
    'SELECT 
        PurchaseOrderNumber
     FROM Sales.SalesOrderHeader
     WHERE PurchaseOrderNumber = @PurchaseOrderNumber
		AND PurchaseOrderNumber > '''''

EXEC dbo.sp_executesql 
    @TSQL,
    N'@PurchaseOrderNumber nvarchar(25)',
    @PurchaseOrderNumber

• 參考資料
• How to Overcome the SQL Server Filtered Index UnMatchedIndexes Issue
• Filtered Indexes and Dynamic SQL