SQL Server CTE Basics

The CTE was introduced into standard SQL in order to simplify various classes of SQL Queries for which a derived table just wasn't suitable. For some reason, it can be difficult to grasp the techniques of using it. Well, that's before Rob Sheldon explained it all so clearly for us.

Introduced in SQL Server 2005, the common table expression (CTE) is a temporary named result set that you can reference within a SELECT, INSERT, UPDATE, or DELETE statement. You can also use a CTE in a CREATE VIEW statement, as part of the view’s SELECT query. In addition, as of SQL Server 2008, you can add a CTE to the new MERGE statement.

SQL Server supports two types of CTEs-recursive and nonrecursive. In this article, I explain how to create both types. The examples I provide are based on a local instance of SQL Server 2008 and retrieve data from the AdventureWorks2008 sample database.

Working with Common Table Expressions

You define CTEs by adding a WITH clause directly before your SELECT, INSERT, UPDATE, DELETE, or MERGE statement. The WITH clause can include one or more CTEs, as shown in the following syntax:

…which can be represented like this…


As you can see, if you include more than one CTE in your WITH clause, you must separate them with commas. In addition, for each CTE, you must provide a name, the AS keyword, and a SELECT statement. You can also provide column names (separated by commas), as long as the number of names match the number of columns returned by the result set.

The SELECT statement in your CTE query must follow the same requirements as those used for creating a view. For details about those requirements, see the topic “CREATE VIEW (Transact-SQL)” in SQL Server Books Online. For more details about CTEs in general, see the topic “WITH common_table_expression (Transact-SQL).”

After you define your WITH clause with the necessary CTEs, you can then reference those CTEs as you would any other table. However, you can reference a CTE only within the execution scope of the statement that immediately follows the WITH clause. After you’ve run your statement, the CTE result set is not available to other statements.

Creating a Nonrecursive Common Table Expression

A nonrecursive CTE is one that does not reference itself within the CTE. Nonrecursive CTEs tend to be simpler than recursive CTEs, which is why I’m starting with this type. In the following example, I create a CTE named cteTotalSales:

After I specify the CTE name, I provide two column names, SalesPersonID and NetSales, which are enclosed in parentheses and separated by a comma. That means the result set returned by the CTE query must return two columns.

Next, I provide the AS keyword, then a set of parentheses that enclose the CTE query. In this case, the SELECT statement returns the total sales for each sales person (total sales grouped by salesperson ID). As you can see, the CTE query can include Transact-SQL functions, GROUP BY clauses, or any elements that the SELECT statement in a view definition can include.

I can now reference cteTotalSales in the statement that immediately follows. For this example, I create a SELECT statement that joins the Sales.vSalesPerson view to cteTotalSales, based on the salesperson ID. I then pull the names and locations from the view and the net sales from the CTE. The following table shows the results returned by this statement.


As you saw earlier in the syntax, you can include multiple CTEs in a WITH clause. The following WITH clause includes two CTEs, one named cteTotalSales and one named cteTargetDiff:

The first CTE-cteTotalSales-is similar to the one in the preceding example, except that the WHERE clause has been further qualified to include sales only from 2003. After I define cteTotalSales, I add a comma, and then define cteTargetDiff, which calculates the difference between the sales total and the sales quota.

The new CTE definition specifies three columns for the result set: SalesPersonID, SalesQuota, and QuotaDiff. As you would expect, the CTE query returns three columns. The first is the salesperson ID. The second is the sales quota. However, because a sales quota is not defined for some salespeople I use a CASE statement. If the value is null, that value is set to 0, otherwise the actual SalesQuota value is used.

The final column returned is the difference between the net sales and sales quota. Again, I use a CASE statement. If the SalesQuota value is null, then the NetSales value is used, otherwise the sales quota is subtracted from the net sales to arrive at the difference.

Something interesting to note about the second CTE query is that I’ve joined the Sales.SalesPerson table to the first CTE-cteTotalSales-so I could calculate the difference between total sales and the sales quota. Whenever you define multiple CTEs in a single WITH clause, you can reference preceding CTEs (but not the other way around).

Once I’ve defined my CTEs, I can reference them in the first statement that follows the CTE, as you saw in the previous example. In this case, I join the Sales.vSalesPerson view to cteTotalSales and then join to cteTargetDiff, all based on the salesperson ID. My SELECT list then includes columns from all three sources. The statement returns the results shown in the following table.


As you can see, sales data is provided for all salespeople, including the city in which they reside, their net sales, their sales quota, and the calculated difference between the two figures. In this case, everyone well exceeds the quota, where a quota has been defined.

Creating a Recursive Common Table Expression

A recursive CTE is one that references itself within that CTE. The recursive CTE is useful when working with hierarchical data because the CTE continues to execute until the query returns the entire hierarchy.

A typical example of hierarchical data is a table that includes a list of employees. For each employee, the table provides a reference to that person’s manager. That reference is itself an employee ID within the same table. You can use a recursive CTE to display the hierarchy of employee data, as it would appear within the organizational chart.

Note that a CTE created incorrectly could enter an infinite loop. To prevent this, you can include the MAXRECURSION hint in the OPTION clause of the primary SELECT, INSERT, UPDATE, DELETE, or MERGE statement. For information about using query hints, see the topic “Query Hints (Transact-SQL)” in SQL Server Books Online.

To demonstrate how the recursive CTE works, I used the following Transact-SQL statements to create and populate the Employees table in the AdventureWorks2008 database:

As you might realize, the AdventureWorks2008 database already includes the HumanResources.Employee table. However, that table now uses the hierarchyid data type to store hierarchical data, which would introduce unnecessary complexity when trying to demonstrate a recursive CTE. For that reason, I created my own table. However, if you want to try out a recursive CTE without creating and populating a new table, you can use the AdventureWorks sample database that shipped with SQL Server 2005. The HumanResources.Employee table in that database stores the data in a way similar to the table I create above.

After I created the Employees table, I created the following SELECT statement, which is preceded by a WITH clause that includes a CTE named cteReports:

As you can see, the CTE returns five columns: EmpID, FirstName, LastName, MgrID, and EmpLevel. The EmpLevel column refers to the level in the hierarchy in which the employees fit. The highest level of the hierarchy is 1, the next level is 2, followed by 3, and so on.

The CTE query is itself made up of two SELECT statements, connected with the UNION ALL operator. A recursive CTE query must contain at least two members (statements), connected by the UNION ALL, UNION, INTERSECT, or EXCEPT operator. In this example, the first SELECT statement is the anchor member, and the second statement is the recursive member. All anchor members must precede the recursive members, and only the recursive members can reference the CTE itself. In addition, all members must return the same number of columns with corresponding data types.

Now lets look closer at the statements themselves. The first statement, the anchor member, retrieves the employee ID, first name, last name, and manager ID from the Employees table, where the manager ID is null. This would be the employee at the top of the hierarchy, which means this person reports to no one. Consequently, the manager ID value is null. To reflect that this person is at the top of the hierarchy, I assign a value of 1 to the EmpLevel column.

The second statement in the CTE query-the recursive member-also retrieves the employee ID, first name, last name, and manager ID for employees in the Employees table. However, notice that I join the Employees table to the CTE itself. In addition, the join is based on the manager ID in the Employees table and the employee ID in the CTE. By doing this, the CTE will loop through the Employees table until it returns the entire hierarchy.

One other item to notice about the second statement is that, for the EmpLevel column, I add the value 1 to the EmpLevel value as it appears in the CTE. That way, each time the statement loops through the hierarchy, the next correct level is applied to the employees at the level.

After I define my WITH clause, I create a SELECT statement that retrieves the data from the CTE. Note, however, that for the Manager column, I retrieve the first and last name of the employee associated with the manager ID in the CTE. This allows me to display the full name of the manager for each employee. The following table shows the result set returned by the SELECT statement and its CTE.


As you can see, the CTE, whether recursive or nonrecursive, can be a useful tool when you need to generate temporary result sets that can be accessed in a SELECT, INSERT, UPDATE, DELETE, or MERGE statement. In a sense, a CTE is like a derived table: it’s not stored as an object and is valid only during the execution of the primary statement. However, unlike the derived table, a CTE can be referenced multiple times within a query and it can be self-referencing. And best of all, CTEs are relatively easy to implement.

You’ll have noticed that Bob is using AdventureWorks2008 rather than AdventureWorks. If you prefer to run these examples against the AdventureWorks database rather than the AdventureWorks2008 database, you should change the BusinessEntityID column to the SalesPersonID column.


Tags: , , , , , ,


  • Rate
    [Total: 30    Average: 4.2/5]
  • jrara

    Where’s BusinessEntityID?
    Thanks for this post. I tried to run these statements on my SQL Server 2008 EE with AdventureWorks installed, but it says that:

    Msg 207, Level 16, State 1, Line 31
    Invalid column name ‘BusinessEntityID’.

    I could not find this column from Sales.vSalesPerson. Where is it?

  • Andrew Clarke

    Re: Where’s BusinessEntityID?
    Thanks for letting us know. We’ve added a note to make it more obvious that these examples are designed to work with AdventureWorks2008 rather than AdventureWorks. Sadly, someone at Microsoft had the brainwave of altering  things so that the two databases weren’t compatible, so from now on, anyone who uses AdventureWorks for programming examples will hit this problem one way or the other, whichever version he/she chooses..

    If you wish to run these examples against the AdventureWorks database rather than the AdventureWorks2008 database, you should change the BusinessEntityID column to the SalesPersonID column. As Bob says in the article ‘The examples I provide are based on a local instance of SQL Server 2008 and retrieve data from the AdventureWorks2008 sample database.’

  • dmckinney

    railroad diagram?
    I like the “railroad diagram” very much. I’ve not come across this before, but it is easier to get ones head around than the usual bracket and braces approach.

    As for CTEs, I use them a lot (often together with rownumber) but they can on occasions give awful performance. (Replacing with a temp table tends to work better.)

    David McKinney.

  • AndyD

    multiple CTEs
    Thanks for clearly and concisely explaining the syntax for multiple CTEs. I’ve tripped over this problem several times in the past, and never managed to get it right. BOL never seemed to help much either.

    Hopefully the use of a comma will stick in my little head from now on.

    Thanks again,

  • Kaleem Khan

    Why use CTE’s
    I agree with someone above quoting regarding the performance. Use A CTE when you want to compute recursively, in many cases it will save you time by avoiding cursors and yet I hope performance of CTE will be better than directly using the cursor. I’m sure a recursive CTE also uses cursor for recusrsion behind the scene but the usage is optimized by interpreter and/or compiler.

  • Tahir

    Tahir Gul
    Very good, comprehensive and to the point article.

    Thanks a lot.

  • egeman

    Recursive CTE
    Great article! The explanation on recursive CTEs is much better than the one in books online.

  • Rajanand

    can CTE be referenced twice in the query?
    I could not able to use CTE in second select query.. why isn’t that possible?
    with temp (column)
    select column from temp
    select max(column) from temp

  • Sumayi

    Show Highest Level of Manager
    In my case,
    My table like as
    106,Jossef ,Goldberg,103

    I need result as
    102,Terri Duffy ,1,NULL
    103,Roberto Tamburello,1 ,NULL
    102,Rob Walters,2,Terri Duffy
    102,Gail Erickson,2,Terri Duffy
    103L,Jossef Goldberg,2,Roberto Tamburello
    103,Dylan Miller,2,Roberto Tamburello
    102,Diane Margheim,3,Gail Erickson
    102,Gigi Matthew,3,Gail Erickson
    103,Michael Raheem,3,Jossef Goldberg

    Could you please tell me the way to do that result ?

  • Gadfly

    Use of USE with CTE
    We have several databases and use Queries like this:

    Use A Select B from C.

    I’ve converted a few to:

    With D as
    Select B from C
    Select * from D

    but I don’t know how to incorporate the instruction Use A.

    Where does the Use go? Can Use be used with CTEs?

  • Frank Fosse

    Recursive CTE returning NULL only
    I am creating a view with the example as shown

    CREATE VIEW dbo.EmployeeLevel
    cteReports (EmpID, FirstName, LastName, MgrID, EmpLevel)
    SELECT EmployeeID, FirstName, LastName, ManagerID, 1
    FROM Employees
    SELECT e.EmployeeID, e.FirstName, e.LastName, e.ManagerID,
    r.EmpLevel + 1
    FROM Employees e
    INNER JOIN cteReports r
    ON e.ManagerID = r.EmpID
    FirstName + ‘ ‘ + LastName AS FullName,
    (SELECT FirstName + ‘ ‘ + LastName FROM Employees
    WHERE EmployeeID = cteReports.MgrID) AS Manager
    FROM cteReports


    If I inspect this view, I find that all columns are defined as null. How can I make the EmployeeID equal to not null (and the view can be used by Entity Framework)?