克服 LIKE 字元長度限制

通過在此處閱讀此LIKE 字元長度限制，我似乎無法在 LIKE 子句中發送超過 ~4000 個字元的文本。

我正在嘗試從查詢計劃記憶體中獲取特定查詢的查詢計劃。

SELECT *
FROM sys.dm_exec_cached_plans AS cp 
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp 
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st
where st.text like '%MY_QUERY_LONGER_THAN_4000_CHARS%' ESCAPE '?'

如果其中的查詢LIKE超過 4000 個字元，那麼即使我的查詢在記憶體計劃中，我也會得到 0 個結果。（我期待至少有一個錯誤）。

有沒有辦法解決這個問題或做不同的事情？我有可能是 >10000字元長的查詢，看起來我無法使用LIKE.

這似乎不能在純 T-SQL 中解決，因為在“搜尋”字元串中既不允許CHARINDEX也PATINDEX不允許使用超過 8000 個字節（即最多 8000VARCHAR或 4000 個NVARCHAR字元）。這可以在以下測試中看到：

SELECT 1 WHERE CHARINDEX(N'Z' + REPLICATE(CONVERT(NVARCHAR(MAX), N'a'), 7000),
                        N'Z' + REPLICATE(CONVERT(NVARCHAR(MAX), N'a'), 6000)) > 0

SELECT 1 WHERE PATINDEX(N'Z' + REPLICATE(CONVERT(NVARCHAR(MAX), N'a'), 7000),
                       N'Z' + REPLICATE(CONVERT(NVARCHAR(MAX), N'a'), 6000)) > 0

這兩個查詢都返回以下錯誤：

消息 8152、級別 16、狀態 10、行 xxxxx

字元串或二進制數據將被截斷。

並且，將7000這些查詢中的任何一個減少到3999消除錯誤。在這兩種情況下的值4000也會出錯（由於N'Z'開頭的額外字元）。

但是，這可以使用 SQLCLR 來完成。創建一個接受兩個類型輸入參數的標量函式相當簡單NVARCHAR(MAX)。

下面的範例使用免費版本的SQL# SQLCLR 庫（我創建了它，但免費版本中再次提供了String_Contains :-) 來說明此功能。

設置

-- DROP TABLE #ContainsData;
CREATE TABLE #ContainsData
(
 ContainsDataID INT NOT NULL IDENTITY(1, 1) PRIMARY KEY,
 Col1 NVARCHAR(MAX) NOT NULL
);

INSERT INTO #ContainsData ([Col1])
VALUES (N'Q' + REPLICATE(CONVERT(NVARCHAR(MAX), N'a'), 15000)),
      (N'W' + REPLICATE(CONVERT(NVARCHAR(MAX), N'a'), 20000)),
      (N'Z' + REPLICATE(CONVERT(NVARCHAR(MAX), N'a'), 70000));

-- verify the lengths being over 8000
SELECT tmp.[ContainsDataID], tmp.[Col1], DATALENGTH(tmp.[Col1])
FROM   #ContainsData tmp;

測試

SELECT tmp.[ContainsDataID], tmp.[Col1], DATALENGTH(tmp.[Col1])
FROM   #ContainsData tmp
WHERE  SQL#.String_Contains(tmp.[Col1], REPLICATE(CONVERT(NVARCHAR(MAX), N'a'), 15100)) = 1;
-- IDs returned: 2 and 3

SELECT tmp.[ContainsDataID], tmp.[Col1], DATALENGTH(tmp.[Col1])
FROM   #ContainsData tmp
WHERE  SQL#.String_Contains(tmp.[Col1], REPLICATE(CONVERT(NVARCHAR(MAX), N'a'), 26100)) = 1;
-- IDs returned: 3

請記住，String_Contains使用的是對所有內容（大小寫、重音、假名和寬度）的比較。

因為您還要求提供替代方法，所以查找特定計劃的另一種方法是搜尋其plan_hash，方法是按如下方式更改您的查詢：

SELECT *
FROM sys.dm_exec_cached_plans AS cp 
INNER JOIN sys.dm_exec_query_stats qs
   ON cp.plan_handle = qs.plan_handle
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp 
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st
WHERE qs.query_hash = 0xE4026347B5F49802

我發現獲取QueryHash要搜尋的值的最快方法是將相關查詢粘貼到查詢視窗中，然後顯示估計的執行計劃。閱讀 XML 輸出並QueryHash在元素中查找屬性，StmtSimple這應該可以滿足您的需求。將 QueryHash 值插入上面的查詢中，希望您應該擁有您要查找的內容。

這是一些螢幕截圖，展示瞭如何快速獲得QueryHash價值，以防我解釋得不好。

顯示預計執行計劃

顯示執行計劃 XM…

搜尋 QueryHash 值

顯然，如果您要查找的查詢與您為其顯示估計執行計劃的查詢不同，則該技巧將不起作用，但這可能比 CLR 常式附帶的所有細微差別更快，並使它們正常工作。

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