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
- 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: