下面的查询

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;

始终获得嵌套循环计划

尝试使用INNER HASH JOIN或INNER MERGE JOIN提示强制执行此问题会产生以下错误.

Query processor could not produce a query plan because of the hints
defined in this query. Resubmit the query without specifying any hints
and without using SET FORCEPLAN.

我发现了一种允许使用散列或合并连接的解决方法 – 将变量包装在聚合中.产生的计划成本显着降低(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;

这种行为的原因是什么？并且有没有比我找到的更好的解决方法？ (这可能不需要额外的执行计划分支)