SQL Server 2005 引入了常量表达式(Common Table Expression CTE)，一个类似于衍生表的T-SQL构造方式。CTE是只存在于单个SQL语句范围内的“临时结果集”。是一个允许在单个SQL语句中访问它的功能，以前只能通过使用函数、临时表、游标等来访问这些功能。和衍生表不同，CTE可以自己引用自己，在一个查询语句中重复引用。还有一点和衍生表不同，即CTE不能关联，即使和APPLY 一起也不行。尽管CTE描述为临时结果集，但不要假设CTE的处理方式与T-SQL的其余部分是分开的。本质上，这仍然是一个派生表，就像我们已经看到的其他例子一样。CTE和衍生表的主要不同点在于自引用。递归CTE通常使用两个(极少使用更多情形)查询，使用 UNION ALL 结合。第一个查询称为“锚成员”(Anchor Member)，可以单独执行以产生结果。第二个查询，“递归成员”(Recursive Member)，引用CTE本身。它使用来自锚成员的数据来生成更多的行，然后递归地使用它自己生成的行继续生成更多的数据。这是逻辑上的定义，我们将很快看到它是如何执行的。
Adventure-Works库中内置存储过程dbo.uspGetEmployeeManagers，在经典的递归练习中使用名为EMP_cte的CTE，列出员工及其管理人员。
CREATE OR ALTER PROCEDURE dbo.uspGetEmployeeManagers @BusinessEntityID INTASBEGIN SET NOCOUNT ON; -- 使用递归查询列出管理者的所有下属员工 WITH EMP_cte(BusinessEntityID, OrganizationNode, FirstName, LastName, JobTitle,RecursionLevel) -- CTE 名称和字段 AS ( SELECT e.BusinessEntityID, e.OrganizationNode, p.FirstName, p.LastName, e.JobTitle, 0 -- 获取第一个员工信息 FROM HumanResources.Employee e INNER JOIN Person.Person AS p ON p.BusinessEntityID = e.BusinessEntityID WHERE e.BusinessEntityID = @BusinessEntityID UNION ALL SELECT e.BusinessEntityID, e.OrganizationNode, p.FirstName, p.LastName, e.JobTitle, RecursionLevel + 1 -- Join recursive member to anchor -- and to the next recursive member FROM HumanResources.Employee e INNER JOIN EMP_cte ON e.OrganizationNode = EMP_cte.OrganizationNode.GetAncestor(1) INNER JOIN Person.Person p ON p.BusinessEntityID = e.BusinessEntityID ) -- Join back to Employee to return the manager name SELECT EMP_cte.RecursionLevel , EMP_cte.BusinessEntityID , EMP_cte.FirstName , EMP_cte.LastName , EMP_cte.OrganizationNode.ToString() AS OrganizationNode , p.FirstName AS 'ManagerFirstName' , p.LastName AS 'ManagerLastName' -- Outer select from the CTE FROM EMP_cte INNER JOIN HumanResources.Employee e ON EMP_cte.OrganizationNode.GetAncestor(1) = e.OrganizationNode INNER JOIN Person.Person p ON p.BusinessEntityID = e.BusinessEntityID ORDER BY RecursionLevel, EMP_cte.OrganizationNode.ToString() OPTION (MAXRECURSION 25)END;GO
您可以看到锚成员(UNION中的第一个查询)都在CTE中，它将根据作为参数传递给它的BusinessEntityID值返回数据。它在代码中被注释为，获取初始雇员。然后，递归发生在UNION ALL中的第二个查询中。它被注释为——连接递归成员以锚定和下一个递归成员。它使用函数get祖先来根据在锚成员中定义的数据检索其他数据。
执行存储过程，并捕获执行计划：
EXEC uspGetEmployeeManagers 9
我们在之前所做的努力现在已经得到了回报。在这个计划中没有任何你以前没有见过的操作符，所以即使它是一个大计划，只要有耐心，它也会相对容易理解。让我们把计划分成几个部分，从右上角开始
我们将按照逻辑调用的顺序，从左到右阅读这个计划片段，从 Index Spool 操作开始，因为这个操作，与我们即将遇到的 Table Spool 操作一起，标识着CTE递归进程的开始。我们知道 Spool 操作是使用临时的 worktable 存储执行计划中需要多次使用或重用的数据。上面递归查询的本质是 SQL Server 在递归构建结果集时存储数据。Index Spool 是 Lazy Spool，是一个流操作，从子操作请求一行数据，存储后，立即传递给其父操作，在它之前的操作符逻辑上将控制权传递回父操作符。
这个例子中，Index Spool 操作的“节点 ID”值是 4，其存储 Concatenation 操作的结果，Concatenation 操作解决了脚本中的 UNION ALL操作。这个操作按照输入顺序，从上到下，处理每个输入。Concatenation 操作总是拥有两个或者更多输入。它调用顶部输入，将检索到的行传递给其父操作，直到它接收到所有行。之后，它继续到第二个输入，重复同样的过程。
本例中，顶部输入搜集 CTE 的“Anchor Member”数据。其为执行两个表HumanResource.Employee 和 Person.Person 上 Clustered Index Seeks 的嵌套循环联接，这返回 BusinessEntityID 是9的雇员的一行数据。紧接着有两个计算标量操作，每个返回一个表达式，两个表达式都被设置为0. 一个是CTE递归等级，另外一个是CTE的衍生列，名为 RecursionLevel。
在顶部输入的所有行都处理完成后，Concatenation 操作转到第二个输入，不再返回到第一个输入。下图为Concatenation 操作的底部输入，其生成Recursive Member。
这里是事情变得有趣的地方。这个计划片段发现每个经理人(直接经理，经理的经理等等)。SQL Server 通过 Table Spool操作，结合顶部输入的 Index Spool，实现递归方法。Table Spool 的“主节点 ID”的值是 4，表明其消费的是加载到 Index Spool 操作的数据。下图为Table Spool 的部分属性：
上图中，“使用堆栈”属性值设置为 True，是递归查询必要部分。以堆栈形式存储数据，意味着新数据总是增加到顶部的，数据页总是从顶部读取的。数据被读取后，即被移除。当你看到“使用堆栈”属性设置为 True，Index Spool 的行为变为“堆栈”。这对于推动CTE的递归计算是至关重要的。当Recursive Member执行时，Table Spool 读取并从Spool中删除Anchor 行。这个计划片段的剩余部分是发现 Anchor 值的经理。经理数据存储在 Index Spool 操作的 Spool中，当Table Spool 准确请求下一行数据时，那行数据会被读取，然后移除。从那里，递归开始继续。Assert 操作的工作是验证查询中的MAXRECURSION(25)，当递归级别超过25时，终止。
Table Spool 返回4行。紧随着Table Spool的计算标量操作，用作计算当前的递归等级，每次加1. 这个数据流形成嵌套循环联接的外部输入，该联接通过内置函数 GetAncestor，联接到Employee表，进而联接到Person 表的 BusinessEntityID。内部输入执行Employee表和Person表的嵌套循环联接。Employee 表的 Clustered Index Scan 属性中，可以看到这个扫描执行的次数。
估计执行次数属性值是4，估计行数是290，总共行数是1160(4*290)，这和Actual Number of Rows 的值一致。如下图：
然后使用筛选器操作。优化器决定使用对表Employee完整扫描，然后，在过滤器操作中，比较每行OrganizationNode和来自CTE的Getancestor，仅仅保留匹配的行。对于处理的前三行(来自锚成员的行和来自递归成员的前两行)，此筛选器只保留一行，即雇员的直接经理。处理的第四行是CEO，他上头没有经理，因此过滤器现在根本不返回任何行，递归停止。因此，计划最右边的部分总共返回四行:一行来自锚成员，三行来自递归成员，列出了员工的经理，一直到CEO。
因此，我们从锚节点得到一行，从递归成员得到三行，从而使得从连接操作中总共得到四行，但是在最终结果中只返回三行。递归过程完成后,我们做一个内连接返回的每一行,他们的经理,此时,最后一行返回的递归CTE, CEO,未能找到ManagerFirstName和ManagerLastName数据列和行是丢失了。
想要了解更多精彩文章，关注公众号吧：