Using Common Table Expressions in SQL Server

In this post I would like to talk about Common Table Expressions or CTE .  I know there are samples of how to use CTE in BOL,MSDN and elsewhere but I thought that it would be a good idea to give it a try with hands-on examples.CTEs were introduced in SQL Server 2005. You can think of them as temporary views.They last only for the duration of the execution of the query.You can think of CTEs as an alternative to using subqueries,derived tables and temporary tables.They are similar to derived tables but you can reference them multiple times in the same query.So a CTE is really a named expression and their structure is defined by the query that follows the first CTE statement.We can reference them in SELECT,INSERT,UPDATE,DELETE statements.One other advantage is that with CTEs you can simplify our queries to make them more readable and maintainable.

We will need an instance of SQL Server running in our machine.You can download and install the free SQL Server Express edition from here.I will use SQL Server 2008 R2 Express edition.

Let’s have a look at some CTEs examples.As we go through these examples you will get familiar with CTEs syntax.The syntax of a CTE is not very difficult to understand

We have the WITH keyword and then the CTE name follows.Then we have the column list which is optional.The query appears inside the parentheses after the AS keyword.

I will use theAdventureWorksLt which you can download from this site .

1) Launch SSMS and connect to the instance of SQL Server you have available.In an new query window type the following T-SQL statements.

WITH ListPriceOver500ForProductsPerCategory (ProductNumber, Name, ListPrice) AS
(
SELECT
p.ProductNumber AS productNumber,
c.Name AS categoryName,
p.ListPrice
FROM SalesLT.ProductCategory c
INNER JOIN SalesLT.Product p ON
c.ProductCategoryID = p.ProductCategoryID
WHERE p.ListPrice > 500.0
)

SELECT name AS categoryName, MAX(ListPrice)
FROM ListPriceOver500ForProductsPerCategory
GROUP BY Name

Execute the code above and see the results. In this example I create a CTE where I get the productnumber,category name and list price for all the products where the listprice is over 500. Then I can refer to this CTE in another SELECT query and find the maximum listprice per category where obviously I get back the category names and prices of the products where the list price is greater than 500, since that was defined in the CTE.

2) Now let’s take a look at recursive CTEs.That means that CTEs can reference themselves to produce the desired results.There are two parts in recursive CTEs. We have the base part where it is described what to do when computing the result no longer requires another recursive call.The second part is known as the recursive step.In this step it is required to invoke the recursive call again in order to reach the base part/stage.To put it simply the CTE is built and then the results from that CTE are called recursively in a UNION statement that returns subsets of data until all the data is returned. By that we have the chance to create data in a hierarchical fashion.

In a recursive CTE is consisted from a minimum of two queries. The nonrecursive query is called anchor member. The recursive member is the recursive query.  Those two queries are separated by a UNION ALL statement. Anchor members are invoked only once. Recursive members are invoked repeatedly until the query returns no rows.

In an new query window type the following T-SQL statements.

USE tempdb
GO

CREATE TABLE FootballInfo(FootballRelatedPosition_Name nvarchar(50),Football_ID INT PRIMARY KEY,
ReportsToManager INT)

INSERT INTO FootballInfo VALUES('FA',1,  NULL)
INSERT INTO FootballInfo VALUES('Kenny Dalglish',2,  1)
INSERT INTO FootballInfo VALUES('Steven Gerrard',3,  2)
INSERT INTO FootballInfo VALUES('Harry Redknapp',4,  1)
INSERT INTO FootballInfo VALUES('Gareth Bayle',5,  4)
INSERT INTO FootballInfo VALUES('Alex Ferguson',6,  1)
INSERT INTO FootballInfo VALUES('Dirk Kuyt',7,  2)
INSERT INTO FootballInfo VALUES('Aaron Lennon',8,  4)
INSERT INTO FootballInfo VALUES('Pepe Reina',9,  2)
INSERT INTO FootballInfo VALUES('Luka Modrić',10,  4)
INSERT INTO FootballInfo VALUES('Rio Ferdinand',11,  6)
INSERT INTO FootballInfo VALUES('Wayne Rooney',12,  6)

WITH FindwhoReportsToWhom(FootballRelatedPosition_Name,Football_ID,  ReportsToManager)
AS
(SELECT FootballRelatedPosition_Name, Football_ID, ReportsToManager
FROM FootballInfo WHERE Football_ID = 1
UNION ALL
SELECT f.FootballRelatedPosition_Name, f.Football_ID, f.ReportsToManager
FROM FootballInfo f  INNER JOIN
FindwhoReportsToWhom fwr ON fwr.Football_ID = f.ReportsToManager
)

SELECT fwrs.FootballRelatedPosition_Name AS Footballer, fr.FootballRelatedPosition_Name AS 'Manager or FA'
FROM FindwhoReportsToWhom fwrs INNER JOIN FootballInfo fr
ON fwrs.ReportsToManager = fr.Football_ID

In the first T-SQL statements we create a table with football related data.Each football person/authority has an ID, a name and another field(ReportsToManager) that self-references back to the ID field.Then I insert some values into the table. We want to write a query that returns all the football people and who they report to.In this example I will look for Football_ID=1.I write the CTE and the recursion starts at Football_ID=1 which is the anchor member.It picks up every record that reports to Football_ID=1 using the recursive member(SELECT statement after UNION ALL).Then we have subsequent recursions that gives us back all the football people and who they report to.The recursion stops when there are no results to show anymore. Be careful on how you design your CTE, so there is not an infinite loop.

Execute the code and see the results for yourself.

If you have followed everything correctly you should see something similar to the picture below.

Hope it helps!!!