SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software Kodyaz Development Resources
Development resources, articles, tutorials, samples, codes and tools for .Net, SQL Server, Vista, etc.




download SQL Server 2014



New Features in SQL Server 2005 - Common Table Expression (SQL CTE)

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;

sql-server-cte-common-table-expression

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

sql-recursive-cte-query

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"






Follow Kodyaz on Twitter

Related SQL Resources

SQL Server Articles

SQL Server 2012

SQL Server Tools

SQL Blog

SQL Server 2008 Blog

Certification Exams Blog

Reporting Services Blog

Analysis Services Blog

MS SQL Server Forums



Free Exam Vouchers









Copyright © 2004 - 2014 Eralper Yilmaz. All rights reserved.
Community Server by Telligent Systems