Introduction to Common Table Expressions

In this article, we will take a look at the concept of Common Table Expressions in SQL Server 2005 along with sample code on their usage. A Common Table Expression is defined by MS as a "Temporary Named Resultset".

CTEs provide functionality similar to Views, except the definition of the CTE is not stored in the SQL Server metadata.

CTEs allow creating a modular solution which functions similar to a temporary table and can be referenced within functions, stored procedures, views, within other CTEs and also recursively..

Among the advantages of CTEs is the capability to reference the resulting "table" (resultset) multiple times in the same statement. CTEs can be used as part of Select/Insert/Update/Delete statements.

CTEs provide a clean solution for problems which require recursive logic and the benefits of CTEs become the most apparent in these cases.

CTE Lifetime/Scope: CTEs are not "persisted" and have to be used in a Select/Update/Delete/Insert statement following the CTE definition.

Usage Scenario: Simple CTE: The following example creates a CTE named "CTE_Assets"

Data resulting from the CTE retrieved and filtered on the Asset Status and the aggregated AuditCount .

Code Listing 1: Simple CTE

WITH CTE_Assets (Asset_Id, AssetTag, Status, LastAudited, AuditCount)

(

SELECT Assets.AssetId, Assets.AssetTag, Assets.Status,

MAX(AssetAudit.Audit_Date), COUNT(AssetsAudit.AuditId)

FROM Assets LEFT JOIN AssetAudit

ON Assets.AssetId = AssetAudit.AssetId

GROUP BY Assets.AssetId, Assets.AssetTag, Assets.Status

)

SELECT AssetId, AssetTag, Status, LastAudited, AuditCount

FROM CTE_Assets

WHERE Status = 'In Use' and AuditCount > 0

Figure 1: Results of Code Listing 1: Simple CTE

Usage Scenario: Join CTEs

The following code sample defines 2 CTEs - cte_Assets and cte_Locations within the same WITH statement. Data is retrieved by joining the 2 CTEs.

Code Listing 2: Join multiple CTEs

WITH CTE_Assets (Asset_Id, AssetTag, Status, LocationID)

( SELECT Assets.Asset_Id, Assets.AssetTag, Assets.Status

FROM Assets

cte_locations (LocationID, LocationCode, LocationDesc) as

(

SELECT LocationID, LocationCode, LocationDesc

FROM Locations

)

SELECT Asset_Id, AssetTag, Status, LocationCode, LocationDesc

FROM CTE_Assets INNER JOIN CTE_Locations

ON CTE_Assets.LocationID = CTE_Locations.LocationID

WHERE CTE_Assets.Status = 'In Use'

Figure 2: Results of Code Listing 2: Join multiple CTEs

Usage Scenario: Recursive CTEs: Specifying a Recursive CTE involves the following steps

Define one base anchor component and one recursive component.
The anchor member defined in step (1) returns the base resultset.
The recursive member returns the hierarchical data related to the parent resultsets
The final result is a UNION of data retrieved by (2) and (3) combined.
The anchor member allows the recursion repetition to end when the anchor member returns a null result set.

The Locations table in the following example contains locations data which has a hierarchical structure. A location can have zero or more than zero child locations. Any location can have zero or one parent. The CTE illustrates a recursive approach for retrieving the hierarchical data.

Code Listing 3: Recursive CTE

WITH cte (LocationID, LocationCode, LocationDesc, LocationParent) as

(

SELECT LocationID, LocationCode, LocationDesc, LocationParent

FROM Locations

Where LocationParent IS NULL

UNION ALL

SELECT Location.LocationID, Location.LocationCode, Location.LocationDesc,

Location.LocationParent

FROM cte INNER JOIN Location

ON Location.LocationParent = CTE.LocationID

)

SELECT LocationID, LocationCode, LocationDesc, LocationParent FROM CTE

Figure 3: Results of Code Listing 3: Recursive CTE

The above example can be expanded to fetch details on the Parent Location.

MaxRecursion: The value specified for MAXRECURSION limits the levels of recursion

Code Listing 4: Recursive CTE restricted by MAXRECURSION limit.

WITH cte (LocationID, LocationCode, LocationDesc, LocationParent) as

(

SELECT LocationID, LocationCode, LocationDesc, LocationParent

FROM Locations

Where LocationParent IS NULL

UNION ALL

SELECT Location.LocationID, Location.LocationCode,

Location.LocationDesc, Location.LocationParent

FROM cte INNER JOIN Location

ON Location.LocationParent = CTE.LocationID

)

SELECT LocationID, LocationCode, LocationDesc, LocationParent FROM CTE

Option (Maxrecursion 2)

Conclusion:

This article aims in demonstrating the use of SQL Server Common Table Expressions through simple examples. Detailed samples are available in websites listed in the Resources section.

Resources:

Common Table Expressions (CTE) in SQL Server 2005 (Scott Mitchell)
Sorting Hierarchical CTE's (Niels Blog)
SQL Server 2005 Recursive Functions (Dan Sullivan)
MSDN and SQL Server Books Online