Over the last few hours, I've had the fun of getting to answer some questions about Common Table Expressions (CTEs). CTE's are a new feature of SQL Server 2005 that simplify some tasks that are otherwise hard to do with T-SQL. But what isn't always that clear is exactly how the work for recursive queries. These are queries where the inside of the CTE has two parts: an anchor query and the part that actually does the recursion. The anchor query executes just once, but the recursive part executes once for each row returned by the anchor query, and then once again for each row in the recursive part. The recursive part in executed until all of the data SQL Server can find matching the query plan has been exhausted. But there's always the question -- just how deep is this recursion per execution and what order does the recursion occur?
Turns out that this is straight forward: the recursion per iteration is shallow -- just the next level. The recursion is also depth-first. That is, each row in from the calling result set is recursed on serially, but these values are aggregated into a single result set. This behavior is best demonstrated by this query:
use adventureworks
go
;with emps(rn,lvl,employeeID,ManagerID,LoginID) as
(select row_number() over(order by employeeID),1,employeeID,managerID,loginid
from humanresources.employee
where managerID is null
union all
select row_number() over(order by e.employeeID),emps.lvl+1,e.employeeID,e.managerID,e.loginid
from humanresources.employee e
join emps on e.managerid=emps.employeeid)
select rn,lvl,employeeid,managerid,loginid
from emps
where lvl in (2,3)
This produces results like this:
Focus on row four for a moment. Notice that "rn" value is one, indicating that this is the first row returned from a query, but the "lvl" value is four, meaning this person reports to somebody who reports to somebody (the level three person, in this case syed0, shown on row three.) We know that Lynn0 reports to Syed0 because that person's manager's ID matches Syed0's employee ID. However, Lynn0 is the only person that reports to Syed0. Now look at rows five through seven. These workers are also row level 4, but report to Amy0 (row two), so the "rn" reflects a new row count. This demonstrates the depth-first nature of the recursive queries. However, we also know that all of the rows from all of recursions for that row are eventually aggregated together to form a single result set as evidenced by the "lvl" value.
The other interesting thing I got to use CTEs for is to generate a set of currency-specific data. I need to generate prices for a table-full of items -- all priced in US Dollars -- into lists of prices for those same parts in. So I created a table with conversion factors for about 75 world currencies and ran a query like this one:
with Inv(PartNumber,Descr,ListPrice,CurrencySymbol) as
(
select pl.PartNumber,pl.Descr,pl.ListPrice,pl.CurrencySymbol
from dbo.PriceList pl
)
insert into dbo.PriceList
(PartNumber,Descr,ListPrice,CurrencySymbol)
select Inv.PartNumber,Inv.Descr,cast(Inv.ListPrice*C.Factor as money),c.ToSymbol
from f, dbo.CurrencyConversions c
This generates a complete set of items -- complete with currency conversions -- for each item for each currency.