設置變數時使用過濾索引

我從其他問題和文章中知道，當 SQL 編譯查詢計劃時，如果保證每次查詢執行時都能使用過濾索引，則它只能使用過濾索引。這意味著您不能在 where 子句中使用變數，因為有時它可能能夠使用過濾後的索引，有時則不能。

解決此問題的一種方法是使用OPTION(RECOMPILE)，以便它可以使用它的次數，它將獲得過濾後的索引。

做一些測試，我發現這個查詢可以使用過濾索引（注意，我強制索引只是為了證明一點）：

SELECT MAX(table1.SomeDateField)
FROM        dbo.table1 WITH(INDEX(MyFilteredIndex))
WHERE       table1.filteredColumn = @variable
OPTION (RECOMPILE)

但是，如果我想將結果分配給一個變數，我很不走運：

SELECT @OutputVariable = MAX(table1.SomeDateField)
FROM        dbo.table1 WITH(INDEX(MyFilteredIndex))
WHERE       table1.filteredColumn = @variable
OPTION (RECOMPILE)

結果是：

消息 8622，級別 16，狀態 1，第 15 行 由於此查詢中定義的提示，查詢處理器無法生成查詢計劃。在不指定任何提示且不使用 SET FORCEPLAN 的情況下重新送出查詢。

當我不想將輸出保存到變數時，查詢可以清楚地使用過濾後的索引，因為它執行 find 。

我有辦法將此查詢重寫為硬程式碼@variable以消除問題，但有人可以解釋為什麼第一個查詢可以使用過濾索引，但第二個查詢不能嗎？

允許您使用帶有RECOMPILE提示的過濾索引的優化稱為“參數嵌入優化”。這是查詢解析器用變數內的文字值替換變數引用的過程。

請參閱 Paul White 的這篇文章，了解它在第二種情況下不起作用的原因：參數嗅探、嵌入和重新編譯選項

在一種情況下，使用OPTION (RECOMPILE)不會導致應用參數嵌入優化。如果語句分配給變數，則不嵌入參數值：

引用自：https://dba.stackexchange.com/questions/281781