Wednesday, April 27, 2005

SQL Server 2005 TSQL Enhancements: Common Table Expressions

A CTE is composed of an expression name, optional column list,
and a defining query.
Once defined, the CTE may be referenced in the executing SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement
for example:

use Northwind;
with Customers_CTE
as
(
select ContactName,CompanyName from Customers
)
select * from Customers_CTE


the beautiful thing about CTEs is that you can use them to create recursive queries
say you have the following table Employees(EmpID,Name,ManagerID) and you want to select
each employee and his level in the employees hierarchy (with 0 as the top level) this can
be accomplished with the following CTE

With Emps_CTE (EmpID,Name,Level)
As
(
SELECT EmpID,Name, 0 AS Level
FROM Employees
WHERE ManagerID IS NULL

Union All

Select Employees.EmpID,Employees.Name,Emps_CTE.Level+1 as Level
FROM Emps_CTE join Employees
on Emps_CTE.EmpID=Employees.ManagerID
)
Select * FROM Emps_CTE


Note that the defining query must contain at least two SELECT statements
combined by a UNION ALL operator

No comments: