為什麼添加 WHERE 子句會破壞我的工作查詢，並出現錯誤“查詢處理器無法生成查詢計劃，因為定義了提示……”？

我有以下（愚蠢地簡化）查詢，它利用兩個 CTE 引用同一個表並將它們相互連接：

WITH CTE1 AS
(
   SELECT dbo.RemoveNonNumericCharacters(PhoneNumber) AS PhoneNumberCleaned
   FROM PhoneNumbersTable
   GROUP BY dbo.RemoveNonNumericCharacters(PhoneNumber)
),
CTE2 AS
(
   SELECT CTE1.PhoneNumberCleaned
   FROM CTE1
   INNER HASH JOIN PhoneNumbersTable
       ON CTE1.PhoneNumbersCleaned = dbo.RemoveNonNumericCharacters(PhoneNumbersTable.PhoneNumber)
   WHERE PhoneNumbersTable.AreaCode IN (718, 212)
)

SELECT PhoneNumberCleaned
FROM CTE2

注意HASH JOIN裡面發生的事情CTE2。到目前為止，這一切都運作良好。

如果我將以下WHERE子句添加到最終SELECT查詢中，那麼我的整個查詢現在變為：

WITH CTE1 AS
(
   SELECT dbo.RemoveNonNumericCharacters(PhoneNumber) AS PhoneNumberCleaned
   FROM PhoneNumbersTable
   GROUP BY dbo.RemoveNonNumericCharacters(PhoneNumber)
),
CTE2 AS
(
   SELECT CTE1.PhoneNumberCleaned
   FROM CTE1
   INNER HASH JOIN PhoneNumbersTable
       ON CTE1.PhoneNumbersCleaned = dbo.RemoveNonNumericCharacters(PhoneNumbersTable.PhoneNumber)
   WHERE PhoneNumbersTable.AreaCode IN (718, 212)
)

SELECT PhoneNumberCleaned
FROM CTE2
WHERE PhoneNumberCleaned = 'SomePhoneNumberInTheResultSet' -- E.g. 7183998888

然後我得到經典錯誤：

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

僅當我在WHERE子句中使用的值實際存在於結果集中時，才會發生這種情況。如果我選擇任何不存在的值，那麼我不會收到上述錯誤。

現在顯然我的例子對於正在發生的事情有點愚蠢，我可以用幾種不同的方式重寫它來修復它，但我更好奇*為什麼會發生這種情況？*如果 SQL Server 引擎能夠生成返回所有記錄的查詢計劃，為什麼它無法在該查詢計劃的末尾為我在WHERE子句中過濾的標量值添加額外的過濾運算符？

這是我的函式的黑盒程式碼dbo.RemoveNonNumericCharacters（注意我沒有寫這個）：

CREATE FUNCTION [dbo].[RemoveNonNumericCharacters] (@strText VARCHAR(1000))
RETURNS VARCHAR(1000)
AS
BEGIN
   WHILE PATINDEX('%[^0-9]%', @strText) > 0
   BEGIN
       SET @strText = STUFF(@strText, PATINDEX('%[^0-9]%', @strText), 1, '')
   END
   RETURN @strText
END

另請注意， 中的列PhoneNumber是PhoneNumbersTable類型VARCHAR(20)。

該問題不包含複製腳本，但由於隱含謂詞使連接謂詞變得多餘，因此經常出現此錯誤。

換句話說，查詢規範中的邏輯含義將內部聯接變成了邏輯​​叉積（簡化後）。這不一定是一件壞事（因為人們傾向於假設交叉產品是）它只是意味著可以以這種方式簡化查詢規範。

雜湊連接需要一個相等謂詞。在優化器考慮連接實現時，如果查詢樹中沒有合適的謂詞，編譯會失敗並出現錯誤。在理想情況下，優化器可能不會簡化滿足提示所需的連接謂詞。

這是我根據問題的文本內容做出的有根據的猜測。如果需要更詳細的解釋，請提供完整的repro並指定環境。

---

作為一個附帶問題，除了 Erik Darling 的建議之外，這裡有一個適用於 SQL Server 2016的確定性純數字標量函式：

CREATE FUNCTION dbo.RemoveNonNumericCharacters
   (@string nvarchar(4000))
RETURNS 
   nvarchar(4000)
WITH 
   SCHEMABINDING, 
   RETURNS NULL ON NULL INPUT
AS
BEGIN
RETURN
   ISNULL(
       CONVERT(nvarchar(4000),
       (
           SELECT This.ch AS [text()]
           FROM OPENJSON(N'[1' + REPLICATE(N',1', LEN(@string) - 1) + N']') AS J
           CROSS APPLY (SELECT 1 + CONVERT(integer, J.[Key])) AS V (v)
           OUTER APPLY (SELECT SUBSTRING(@string COLLATE Latin1_General_100_BIN2, V.v, 1)) AS This (ch)
           WHERE This.ch COLLATE Latin1_General_100_BIN2 LIKE N'[0123456789]'
           ORDER BY V.v
           FOR XML PATH (N'')
       )), 
       N'');
END;

這可能不是效率的最後一句話，但它很有趣。您可以使用CHARINDEX.

對於 SQL Server 2017 及更高版本：

CREATE FUNCTION dbo.RemoveNonNumericCharacters
   (@string nvarchar(4000))
RETURNS 
   nvarchar(4000)
WITH 
   SCHEMABINDING, 
   --INLINE = ON, /* for 2019 */
   RETURNS NULL ON NULL INPUT
AS
BEGIN
   RETURN
       REPLACE(
           TRANSLATE(@string COLLATE Latin1_General_100_BIN2, 
               TRANSLATE(@string COLLATE Latin1_General_100_BIN2, 
                   N'0123456789',
                   N'XXXXXXXXXX'),
               REPLICATE(N'X', LEN(@string))), 
           N'X', N'');
END;

兩者都可以簡單地轉換為內聯表值函式。

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