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
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)
SELECT EmpID,Name, 0 AS Level
FROM Employees

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: