Pokonaj ograniczenie długości znaków JAK

Po przeczytaniu tego ograniczenia długości LIKE tutaj wygląda na to, że nie mogę wysłać tekstu dłuższego niż ~ 4000 znaków w klauzuli LIKE.

Próbuję pobrać plan zapytań z pamięci podręcznej planu zapytań dla określonego zapytania.

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 '?'

jeśli zapytanie wewnątrz LIKEjest dłuższe niż 4000 znaków, to otrzymuję 0 wyników, nawet jeśli moje zapytanie jest w planie pamięci podręcznej. (Spodziewałem się przynajmniej błędu).

Czy istnieje sposób obejścia tego problemu lub zrobienia tego inaczej? Mam zapytania, które mogą być> 10000znakami i wygląda na to, że nie mogę ich znaleźć za pomocą LIKE.

Nie wydaje się, że można to rozwiązać w czystym języku T-SQL, ponieważ ani CHARINDEXnie PATINDEXpozwala na użycie więcej niż 8000 bajtów w ciągu „do wyszukiwania” (tj. Maksymalnie 8000 VARCHARlub 4000 NVARCHARznaków). Można to zobaczyć w następujących testach:

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

Oba te zapytania zwracają następujący błąd:

Msg 8152, poziom 16, stan 10, wiersz xxxxx
Ciąg lub dane binarne zostałyby obcięte.

Zmniejszając 7000liczbę zapytań, aby 3999pozbyć się błędu. Wartość 4000w obu przypadkach również będzie błędna (z powodu dodatkowego N'Z'znaku na początku).

JEDNAK można to osiągnąć za pomocą SQLCLR. Utworzenie funkcji skalarnej, która akceptuje dwa parametry wejściowe typu, jest dość proste NVARCHAR(MAX).

Poniższy przykład ilustruje tę umiejętność przy użyciu darmowej wersji biblioteki SQL # SQLCLR (którą utworzyłem, ale String_Contains jest ponownie dostępny w bezpłatnej wersji :-).

USTAWIAĆ

-- 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;

TESTY

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

Należy pamiętać, że String_Contains korzysta z porównania wszystkiego ( wielkość liter, akcent, Kana i szerokość).

Ponieważ poprosiłeś także o alternatywne podejście, innym sposobem na znalezienie konkretnego planu jest jego wyszukanie plan_hash, zmieniając zapytanie w następujący sposób:

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

Najszybszym sposobem na znalezienie QueryHashwartości do wyszukania jest wklejenie danego zapytania do okna zapytania, a następnie wyświetlenie szacowanego planu wykonania. Przeczytaj dane wyjściowe XML i poszukaj QueryHashatrybutu w StmtSimpleelemencie, a to powinno dać ci to, czego potrzebujesz. Podłącz wartość QueryHash do powyższego zapytania i mam nadzieję, że powinieneś mieć to, czego szukasz.

Oto kilka zrzutów ekranu pokazujących, jak szybko uzyskać QueryHashwartość na wypadek, gdy źle ją wytłumaczę.

Wyświetl szacunkowy plan wykonania

Pokaż plan wykonania XM ...

Wyszukaj wartość QueryHash

Oczywiście sztuczka nie zadziała, jeśli zapytanie, którego szukasz, różni się od zapytania, dla którego wyświetlasz Szacowany plan wykonania, ale może to być szybsze niż wszystkie niuanse, które występują w procedurach CLR i sprawienie, aby działały poprawnie.

Jeśli masz dostęp do tekstów zapytań (co oznacza, że ​​możesz je modyfikować), możesz dodać unikalne komentarze do tych, którzy są zainteresowani:

select /* myUniqueQuery123 */ whatever from somewhere ...

następnie wyszukaj myUniqueQuery123w pamięci podręcznej planu zamiast całego tekstu zapytania:

... where st.text like '%myUniqueQuery123%'

PS. Nie testowany