关于公用表表达式 (CTE)在(递归)多次引用时的效率

起因: http://bbs.csdn.net/topics/391893546

一直按照中间结果集的方式理解CTE，也一直认为执行计划中会生成对应的中间结果集，因此多次引用时CTE应该优于派生表。而实际上CTE只相当于一个宏定义，效率上和重复写派生表没什么区别。

用代码说话

测试数据

if not object_id(N'Tempdb..#InS') is null
	drop table #InS
Go
Create table #InS([ID] int,[HappenDate] Datetime,[Name] nvarchar(2),[Number] int)
Insert #InS
select 1,'2015-1-1',N'苹果',6
Go
 
if not object_id(N'Tempdb..#OutS') is null
	drop table #OutS
Go
Create table #OutS([ID] int,[InSID] int,[HappenDate] Datetime,[Number] int)
Insert #OutS
select 1,1,'2015-1-1',1 union all
select 2,1,'2015-1-2',1 union all
select 3,1,'2015-1-2',1 union all
select 4,1,'2015-1-5',2 union all
select 5,1,'2015-1-10',1
GO

中间结果用CTE

WITH
a AS (
    SELECT ROW_NUMBER() OVER(PARTITION BY InSID ORDER BY HappenDate) ID,
           InSID,
           HappenDate,
           SUM(Number) Number
      FROM #OutS
  GROUP BY InSID,HappenDate
)
,b AS (
    SELECT a.ID,
           a.InSID,
           a.HappenDate,
           InS.Number TodayNumber,
           a.Number OutNumber,
           InS.Number - a.Number RealNumber
      FROM #InS InS
      JOIN a
        ON InS.ID = a.InSID
     WHERE a.ID = 1
    UNION ALL
    SELECT a.ID,
           a.InSID,
           a.HappenDate,
           b.RealNumber TodayNumber,
           a.Number OutNumber,
           b.RealNumber - a.Number RealNumber
      FROM b
      JOIN a
        ON b.InSID = a.InSID
       AND b.ID + 1 = a.ID
)  
    SELECT *
      FROM b
  ORDER BY InSID, ID

执行计划（图一）

中间结果用临时表

    SELECT ROW_NUMBER() OVER(PARTITION BY InSID ORDER BY HappenDate) ID,
           InSID,
           HappenDate,
           SUM(Number) Number
      INTO #a
      FROM #OutS
  GROUP BY InSID,HappenDate

;WITH b AS (
    SELECT a.ID,
           a.InSID,
           a.HappenDate,
           InS.Number TodayNumber,
           a.Number OutNumber,
           InS.Number - a.Number RealNumber
      FROM #InS InS
      JOIN #a a
        ON InS.ID = a.InSID
     WHERE a.ID = 1
    UNION ALL
    SELECT a.ID,
           a.InSID,
           a.HappenDate,
           b.RealNumber TodayNumber,
           a.Number OutNumber,
           b.RealNumber - a.Number RealNumber
      FROM b
      JOIN #a a
        ON b.InSID = a.InSID
       AND b.ID + 1 = a.ID
)  
    SELECT *
      FROM b
  ORDER BY InSID, ID

执行计划（图二）

执行计划（图三）