Dlaczego odwołanie do zmiennej w predykacie łączenia wymusza zagnieżdżone pętle?

Ostatnio natknąłem się na ten problem i nie mogłem znaleźć żadnej dyskusji na ten temat w Internecie.

Zapytanie poniżej

DECLARE @S VARCHAR(1) = '';

WITH T
     AS (SELECT name + @S AS name2,
                *
         FROM   master..spt_values)
SELECT *
FROM   T T1
       INNER JOIN T T2
         ON T1.name2 = T2.name2;

Zawsze otrzymuje plan zagnieżdżonych pętli

Próbując wymusić problem z INNER HASH JOINlub INNER MERGE JOINpodpowiedzi produkuje następujący błąd.

Procesor zapytań nie mógł wygenerować planu zapytania ze względu na wskazówki zdefiniowane w tym zapytaniu. Ponownie wprowadź zapytanie bez podawania wskazówek i bez użycia SET FORCEPLAN.

Znalazłem obejście, które pozwala na użycie sprzężenia mieszającego lub scalającego - zawijanie zmiennej w agregat. Wygenerowany plan jest znacznie tańszy (19,2025 vs 0,261987)

DECLARE @S2 VARCHAR(1) = '';

WITH T
     AS (SELECT name + (SELECT MAX(@S2)) AS name2,
                *
         FROM   spt_values)
SELECT *
FROM   T T1
       INNER JOIN T T2
         ON T1.name2 = T2.name2; 

Jaki jest powód tego zachowania? i czy istnieje lepsze obejście niż to, które znalazłem? (które być może nie wymagają dodatkowych oddziałów planu wykonania)

Próbowałem zapytania w wystąpieniu SQL 2012 i wydaje się, że flaga śledzenia 4199 rozwiązuje problem. Po włączeniu otrzymuję łączenie przez łączny koszt 0,24 i brak dodatkowych oddziałów.

Konkretny artykuł bazy wiedzy dotyczący tego problemu dotyczy problemów z wydajnością, gdy predykat łączenia w zapytaniu ma zewnętrzne kolumny referencyjne w SQL Server 2005 lub SQL Server 2008

Aby dodatkowo się zakwalifikować, TF 4199 umożliwia wszystkie poprawki optymalizatora. Zobacz ten link, aby uzyskać więcej informacji. Włączenie wszystkiego naraz może mieć dziwne skutki uboczne, więc jeśli znajdziesz konkretną poprawkę, lepiej włączyć ją samodzielnie.

Możesz włączyć flagę śledzenia dla poszczególnych zapytań, używając OPTION (QUERYTRACEON 4199);

Stare pytanie, ale wyświetlenie odpowiedzi nie było zbyt ostateczne, pomyślałem, że opublikuję obejście, które znalazłem. Nie jestem pewien, dlaczego optymalizator kwerend ma takie kłopoty HASH, ale myślę, że to nie lubi, MERGEponieważ nie ma posortowanych danych wejściowych. W dniu 2012/14

DECLARE @S VARCHAR(1) = '';

    WITH T
        AS (SELECT TOP (2147483647)
                name + @S AS name2,
                *
            FROM   master..spt_values
            ORDER BY name + @S)
    SELECT *
    FROM   T T1
           INNER JOIN T T2
             ON T1.name2 = T2.name2;

opracowuje następujący plan:

Wymuszanie TOPi ORDER BYin the cte wydają się dawać optymalizatorowi wystarczającą wiedzę na temat zestawu danych do wykonania MERGE JOIN.