Common Table Expressions (CTE)

Common Table Expressions have been available to use since SQL Server 2005 and provide a more elegant way in which to handle a temporary result set other than using a temporary table. The syntax is similar to what I show below

WITH <expression name> ([column name1,column name 2,…) AS
(
SELECT …..

)

One of the main advantages over temporary tables is the ability for you to populate the table with the same expression that you use to create it. If this were a temporary table then we would be stuck with first creating the table and then doing the INSERT statement on it.

Using the AdventureWorks database this is a simple example of using a CTE where we want to pluck out all the loginIDs that begin with B…

WITH BNameSelection (LoginID,ManagerID,EmployeeID)
AS (SELECT LoginID, ManagerID, EmployeeID
   FROM HumanResources.Employee
   WHERE LoginID like ‘%\b%’)
   select * from BNameSelection;

Now it does not just have to be a straight out query that populates the CTE…if we had stuck it in a stored procedure we might have passed a parameter like @NameBegins …

CREATE PROCEDURE PickAName
    — Add the parameters for the stored procedure here
    @NameBegins char(1)
AS
BEGIN

WITH BNameSelection (LoginID,ManagerID,EmployeeID)
AS (SELECT LoginID, ManagerID, EmployeeID
    FROM HumanResources.Employee
    WHERE LoginID like ‘%\’ + @namebegins + ‘%’)
    select * from BNameSelection;

END
GO

Now the REALLY cool thing is that you can also reference the CTE from within the CTE thus making the query recursive. So maybe we would like to get all of the people whom report to the head hancho…It would look like this…

WITH ReportsTo(LoginID,ManagerID,EmployeeID)
AS (SELECT LoginID, ManagerID, EmployeeID
   FROM HumanResources.Employee
   WHERE ManagerID is NULL
   UNION ALL
   SELECT e.LoginID, e.ManagerID, e.EmployeeID
   FROM HumanResources.Employee e
   INNER JOIN ReportsTo r
   ON e.ManagerID=r.EmployeeID)

   select * from ReportsTo;

So pretty cool stuff and the performance is pretty decent. Especially when you consider that you can simplify and condense your logic for a given query.

Hopefully, you’ll feel the need to explore a little more into the concept and get a good benefit for utilizing it.

Cheers!
AJ