SQL Common Table Expression (SQL CTE) on SQL Server
One of the new features in SQL Server 2005 for t-sql developers is SQL CTE (Common Table Expression).
SQL Server Common Table Expressions - CTE, is named as temporary named result set in SQL Server 2005 Books Online.
Please note that most of the sql CTE examples are using data from AdventureWorks sample database build for SQL Server 2005.
SQL developers can download SQL Server 2005 AdventureWorks sample database from CodePlex.
SQL Common Table Expressions - CTE Syntax
A basic Common Table Expression, SQL CTE syntax is as follows:
[ WITH <common_table_expression> [ ,...n ] ]
<common_table_expression>::=
expression_name [ ( column_name [ ,...n ] ) ]
AS
( CTE_query_definition )
The SQL Server CTE name has the rules that applies to sql table names.
If you want to use SPACE character in the CTE name, then you should surround the CTE name with "[" and "]" characters.
SQL Common Table Expression Sample - SQL CTE Sample
Here is a sql CTE sample for t-sql developers.
Please note that you can omit the column names following the t-sql CTE name.
These column name rename the output result set of the CTE select.
For example, the following CTE SQL Server query selects EmployeeID column but when you display contents of the CTE expression, you will see that the values are displayed under column named Id.
WITH [CTE Example] (Id, Title)
AS
(
SELECT EmployeeID, Title FROM HumanResources.Employee
)
SELECT * FROM [CTE Example]
The above CTE sql sample is showing that Common Table Expressions (CTE) can be used as a simple sql view.
Common Table Expression (CTE) as SubSelect
A common table expression, CTE sql expression is basically a t-sql subquery statement in the main sql statement.
Let's consider the below example select CTE SQL Server statements.
WITH [CTE Stock] (ProductID, [Total Quantity])
As
(
Select ProductID, Sum(Quantity)
From Production.ProductInventory
Group By ProductID
)
Select
p.ProductNumber,
p.Name,
cte.[Total Quantity]
From Production.Product p
Inner Join [CTE Stock] cte
On cte.ProductID = p.ProductID
Order By p.ProductNumber;
The same result can be queried without using sql CTE expression as in the following subselect statement.
Select
p.ProductNumber,
p.Name,
(
Select Sum(Quantity)
From Production.ProductInventory
Where ProductID = p.ProductID
) as [Total Quantity]
From Production.Product p
Order By p.ProductNumber;
An important point about the above two CTE queries is that the actual execution plan of both are identical.
Multiple CTE in SQL Server 2005
Another feature in SQL CTE is CTE expressions can be used in cascade mode.
This means a CTE expression can be an input of an other SQL Server CTE expression.
Here is an other SQL Server multiple CTE example code.
WITH [CTE Example] (Id, Title)
AS
(
SELECT EmployeeID, Title FROM HumanResources.Employee
), CTE (Num, EmployeeName)
AS
(
SELECT
CTE.Id,
ISNULL(FirstName,'') + ISNULL(MiddleName,'') + ISNULL(LastName,'')
FROM [CTE Example] CTE
INNER JOIN Person.Contact C
ON CTE.Id = C.ContactID
)
select * from CTE
You can see that there is two sql CTE expressions in the above single t-sql SELECT statement.
SQL developers can also refer to SQL Multiple CTE Syntax and T-SQL CTE Example in SQL Server 2008
SQL Server Recursive Common Table Expressions - Recursive CTE
I'm sure sql developers might not be impressed by the sql CTE Common Table Expressions according to the above t-sql examples.
In fact the read advantage of SQL Server Common Table Expressions CTE, is in sql statements where CTE is referencing to itself in the CTE data.
Such CTE queries are called SQL Server recursive common table expressions
Here is a sample SQL recursive CTE.
WITH Recursive_CTE
AS
(
SELECT * FROM HumanResources.Employee e WHERE ManagerID IS NULL
UNION ALL
SELECT e.*
FROM HumanResources.Employee e
INNER JOIN Recursive_CTE
ON Recursive_CTE.EmployeeID = e.ManagerID
)
SELECT * FROM Recursive_CTE
The SQL Server Recursive CTE queries are formed of two parts.
The first part is called anchor select.
Here is the anchor select of the above t-sql cte expression.
SELECT * FROM HumanResources.Employee e WHERE ManagerID IS NULL
This is the start of SQL select statements.
This is the first row selected by recursive CTE which forms the result set.
The second part is the recursion part of the CTE expression.
It refers to the CTE itself.
Here is the recursive part of the sql CTE statement.
SELECT e.*
FROM HumanResources.Employee e
INNER JOIN Recursive_CTE
ON Recursive_CTE.EmployeeID = e.ManagerID
Note that the above query refers to CTE itself and fetch data from Employee table which is joined by a INNER JOIN.
The following recursive CTE query will return employees and their title who are working under the node of a specific manager in organizational chart.
DECLARE @Employee int
SET @Employee = 12;
WITH CTE_Organization
AS
(
SELECT
EmployeeID, Title, ManagerID, CAST('' as nvarchar(50)) AS Manager_Title
FROM HumanResources.Employee
WHERE EmployeeID = @Employee
UNION ALL
SELECT
e.EmployeeID, e.Title, e.ManagerID, CTE.Title
FROM HumanResources.Employee e
INNER JOIN CTE_Organization CTE
ON CTE.EmployeeID = e.ManagerID
)
SELECT
CTE_Organization.EmployeeID,
C.FirstName,
C.LastName,
CTE_Organization.Title,
CTE_Organization.ManagerID,
CTE_Organization.Manager_Title
FROM CTE_Organization
INNER JOIN Person.Contact C
ON CTE_Organization.EmployeeID = C.ContactID
Using this t-sql recursive CTE query with @Employee parameter, sql developers can query all employees in the hierarchy of the organizational chart.
A Recursive SQL CTE expression can be used as a sql hierarchical query.
SQL developers can use this sql recursive CTE query structure with small modifications on different sql hierarchical query structures refering to the same table.
For example, sql programmers can refer to SQL Recursive Function Sample - MS SQL Server Recursive T-SQL Split Function
One last important note on SQL Server CTE expressions syntax is that before the CTE select statement the last sql statement should be terminated with ";".
After ";", you can code your sql CTE statement otherwise.
If there is not a ";" between the previous sql statement and the tsql CTE statement, the following error will occur:
Msg 319, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.
You can over come this sql error by starting the sql codes of the CTE - Common Table Expression as ";WITH"