Introduction to Common Table Expressions – SQL Server 2005

This article delves into the concepts of Common Table Expressions (CTE) in SQL Server 2005 and their usage.

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)

AS

(

   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)

AS

(    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

1. Define one base anchor component and one recursive component. 
2. The anchor member defined in step (1) returns the base resultset. 
3. The recursive member returns the hierarchical data related to the parent resultsets
4. The final result is a UNION of data retrieved by (2) and (3) combined. 
5. 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