Common Table Expressions (CTE) in SQL Server

Introduction

“Tune queries with this one simple trick: TRUNCATE TABLE”. 

Did I get your attention? Good!

Common Table Expressions or CTE as we will refer to them going forward are a useful tool to quickly create "views" of data inside SQL Server and are an excellent tools for making your queries easier to read and in most cases perform a bit faster. We are going to look at them from a very basic standpoint today but I can promise you we will see them again soon.

The What

Added to SQL server in 2005, a CTE is a view of data you can create on the fly for a single query to work with. There are a few rules that have to be followed in SQL server that will seem quirky at first but there are reasons for each.

• The CTE statement should begin with a semicolon. Any commands before a CTE must be terminated with a semicolon. If your CTE is the first thing in the batch it doesn't have to have it but if someone comes along and adds something in from of it, the whole batch will not work. This is just good practice. Example ";WITH [My_CTE] AS"... 
• CTE follow all rules that govern the creation of views. Remember I told you they were a dynamic view? Stands to reason!
• CTE can be recursive.
• SQL has a maximum recursion value of 100. If you need to go over this amount, you must use the MAXRECURSION hint.

The basic structure of a CTE statement is:
[WITH <common_table_expression> [,...]]

<common_table_expression>::=

cte_name [(column_name [,...])]

AS (cte_query)

The How

Here are some basic examples of CTE operations.
This is an example of a basic CTE that filters out some values from a table before querying it.

-- Find new employees
;WITH [CTE]([EmployeeName], [EmployeeNumber], [HireDate]) AS 
( SELECT TOP 100
 [EmployeeName], 
 [Number], 
 [DateofHire]
  FROM [dbo].[tblEmployees] WITH(NOLOCK)
  WHERE [DateofHire] > '01/01/2005'
  )
SELECT * FROM [CTE]
WHERE [EmployeeName] LIKE 'a%'

This is an example of a recursive CTE. It is very basic and counts to 100. Change the where clause to 1002 and see what happens when you run it.

;WITH [CTE]([Number]) AS 
( SELECT 1
    UNION ALL
      SELECT [Number]+1 FROM CTE WHERE [Number] < 100 
  )
SELECT [Number] FROM [CTE]
OPTION (MAXRECURSION 1000)  

This is an example of a multiple table CTE where we selected our senior employees and our new employees from the same table and output a list of the new employees and which senior employee is assigned as their mentor.

;WITH [NEWBZ]([EmployeeName], [EmployeeNumber], [HireDate], [Mentor]) AS 
( SELECT 
 ([FirstName] + ' ' + [LastName]), 
 [EmployeeNumber], 
 [DateofHire],
 [MentorID]
  FROM [dbo].[Employees] WITH(NOLOCK)
  WHERE [DateofHire] > '01/01/2005' 
),
[OldSalt]([ID], [EmployeeName], [EmployeeNumber], [HireDate]) AS 
( SELECT 
 [ID],
 ([FirstName] + ' ' +  [LastName]), 
 [EmployeeNumber], 
 [DateofHire]
  FROM [dbo].[Employees] WITH(NOLOCK)
  WHERE [DateofHire] < '01/01/2005'
)
SELECT
 [NEWBZ].[EmployeeName] AS [Newb],
 ISNULL([OldSalt].[EmployeeName], 'None') AS [Mentor]
FROM [NEWBZ]
LEFT JOIN [OldSalt] ON [NEWBZ].[Mentor]=[OldSalt].[ID]

Conclusion

As you can see, CTE can be useful for filtering your data before joining it, minimizing the amount of data you are going to be dealing with or just as a means of making a query easier to read and understand. Think of ways you can limit your WHERE clauses and put them to use. If you have a specific situation you would like example use on, leave a comment on it.