intro
Let’s explore everything you need to know about SQL Server CTE statements, including what they are and how they can help you write more readable queries.
To extract the data of interest from an SQL Server database, you might need to run multiple queries. Executing them one at a time at the application level can result in significant overhead. While SQL subqueries can help, they often fall short in terms of reusability. This is where a WITH clause with the right SQL Server CTE statements can make a difference!
In this article, you will look at the definition of a CTE in SQL server, how to use the WITH clause, and see some examples of regular and recursive CTEs.
Let's dive in!
What Is a CTE in SQL Server?
A CTE, short for Common Table Expression, is a temporary named result set that you can reference within the execution scope of a SELECT, INSERT, UPDATE, or DELETE statement. An SQL Server CTE is defined using the WITH keyword, followed by the CTE name and a query that defines its result set.
In SQL Server, a common table expression can also include references to itself. This is referred to as a recursive common table expression (or recursive CTE).
The CTE SQL Server clause can also be used in a CREATE VIEW statement as part of its defining SELECT statement. Find out more in our guide on SQL views.
How to Define a Common Table Expression in SQL Server
In SQL Server, a CTE is defined through the WITH clause. Each SQL Server CTE consists of two elements:
Here is the high-level syntax to define an SQL Server common table expression:
1
WITH cte_name (column_1, column_2, ..., column_N) AS (
2
cte_definition_query
3
)
4
main_query;
Where:
For a more complete syntax, refer to the sample SELECT query below with a CTE:
1
WITH cte_name (column_1, column_2, ..., column_N) AS (
2
SELECT column_a, column_b, ..., column_n
3
FROM table_name
4
WHERE cte_condition
5
)
6
SELECT *
7
FROM cte_name
8
WHERE query_condition;
Note that the same WITH clause can define several CTEs, each with its name and specification query:
1
WITH cte_name_1 (column_1, column_2, ..., column_N) AS (
2
SELECT column_a, column_b, ..., column_n
3
FROM table_name_1
4
WHERE cte_condition_1
5
),
6
...
7
cte_name_M (column_1, column_2, ..., column_N) AS (
8
SELECT column_x, column_y, ..., column_n
9
FROM table_name_2
10
WHERE cte_condition_2
11
)
12
SELECT *
13
FROM table_X
14
WHERE query_condition;
The main query can then use the result sets of the different CTEs, for example, to join them or use them in IN operators within WHERE conditions.
Key Aspects of an SQL Server CTE
Guidelines for Creating and Using CTEs in SQL Server
To properly create and work with Common Table Expressions in SQL Server, you need to follow key rules some of which are as follows:
Adherence to these four CTE guidelines and rules will make your job as a DBA easier. For all guidelines, refer to the official documentation.
Guidelines for Creating and Using Recursive CTEs in SQL Server
For all other guidelines, check out the official documentation.
SQL Server CTE Example List
Now that you know what a CTE is and how it works in SQL Server, you are ready to explore some SQL Server CTE examples!
Note: The sample query below will be executed in DbVisualizer, a fully-featured database client that fully supports SQL Server and dozens of other databases. Anyway, any other database client will do.
Example 1: Getting an Organization’s Hierarchy
Assume your Employees table has the EmployeeID, FullName, and ManagerID columns. Your goal is to get the organizational hierarchy under a specific employee.
Here's how you can get that data using a recursive CTE in SQL Server:
1
WITH EmployeeHierarchy AS (
2
SELECT EmployeeID, FullName, ManagerID
3
FROM Employees
4
WHERE EmployeeID = 3
5
UNION ALL
6
SELECT E.EmployeeID, E.FullName, E.ManagerID
7
FROM Employees E
8
JOIN EmployeeHierarchy EH
9
ON EH.EmployeeID = E.ManagerID
10
)
11
SELECT *
12
FROM EmployeeHierarchy;
In this query, the CTE EmployeeHierarchy selects the employee with ID 3. Then, it recursively joins the partial result set with the Employees table to retrieve the employees managed by the current manager, continuing to traverse the hierarchy until the entire sub-hierarchy is fetched. This is a common query pattern to traverse a tree structure in SQL Server.
Execute it, and you will get:
Note that you cannot achieve the same result with a simple SELECT query.
Example 2: Retrieving the Department with the Highest Average Salary
Suppose your Employees table has the EmployeeID, DepartmentID, and Salary columns. You want to find out which department has the highest average salary.
You can obtain that information using a CTE SQL Server statement as below:
1
WITH DepartmentAvgSalary AS (
2
SELECT DepartmentID, AVG(Salary) AS AvgSalary
3
FROM Employees
4
GROUP BY DepartmentID
5
)
6
SELECT TOP 1 D.DepartmentName, DAS.AvgSalary
7
FROM DepartmentAvgSalary DAS
8
JOIN Departments D ON D.DepartmentID = DAS.DepartmentID
9
ORDER BY DAS.AvgSalary DESC;
Here, the CTE DepartmentAvgSalary calculates the average salary for each department. The main query then displays the department with the highest average salary.
Launch it in DbVisualizer and you will obtain:
Example 3: Finding Per-Product Sales in Top Regions
Now, you want to get sales totals by product only in the top-selling regions. You can use an SQL Server CTE involving two auxiliary subqueries as follows:
1
WITH RegionalSales AS (
2
SELECT RegionID, SUM(PriceAmount) AS TotalSales
3
FROM Orders
4
GROUP BY RegionID
5
), TopRegions AS (
6
SELECT RegionID
7
FROM RegionalSales
8
WHERE TotalSales > (
9
SELECT SUM(TotalSales) / (SELECT COUNT(*) FROM Regions)
10
FROM RegionalSales
11
)
12
)
13
SELECT R.RegionName, O.ProductName, SUM(O.Quantity) AS ProductUnits, SUM(O.PriceAmount) AS ProductSales
14
FROM Orders O
15
JOIN Regions R ON O.RegionID = R.RegionID
16
WHERE O.RegionID IN (
17
SELECT RegionID
18
FROM TopRegions
19
)
20
GROUP BY R.RegionName, O.ProductName
21
ORDER BY RegionName;
In this example, the first CTE — RegionalSales — calculates total sales per region. Next, the second CTE — TopRegions — identifies the regions with more sales than the average. The main query finally uses the TopRegions output in the WHERE clause to retrieve sales data only for those top regions.
The result will be:
Et voilà! You are now an SQL Server CTE expert!
Conclusion
In this article, you learned what a CTE in SQL Server is and how to define one. You now understand that common table expressions are a powerful tool for improving query organization and facilitating the reuse of SQL code.
CTEs make your queries longer and more complex, so the need for an advanced database client becomes essential. This is where DbVisualizer comes into play!
Beyond offering the most common features of a database client and support for dozens of DBMSs, DbVisualizer provides advanced query optimization capabilities that can take your SQL Server CTEs to the next level. Download DbVisualizer for free now!
FAQ
How does an SQL Server CTE differ from a regular subquery?
An SQL Server CTE differs from a regular subquery because it allows for easier code reuse within complex queries. CTEs are defined at the beginning of a query using the WITH clause and can be referenced multiple times within the main query. In contrast, regular subqueries are embedded within a single query and cannot be reused or referenced elsewhere in the same query.
What is an SQL Server recursive CTE?
An SQL Server recursive CTE is a type of CTE that references itself to repeatedly execute a query. It consists of two parts: an anchor member, which provides the initial result set, and a recursive member, which iterates over the result set to add subsequent rows. The recursion continues until a specified condition is met. The official docs will provide more information.
Can a single SQL Server query have multiple CTEs?
Yes, a single SQL Server query can have multiple CTEs. You can define several CTEs in a query by separating each with a comma after the initial WITH keyword. Each CTE can be referenced in the main query or within other CTEs.
What is the difference between a CTE and a recursive CTE in SQL Server?
The key difference between a CTE and a recursive CTE in SQL Server is that a regular CTE does not involve recursion while a recursive CTE is designed to perform iterative operations until a condition is met.
What are the pros and cons of WITH queries in SQL Server?
👍 Pros:
👎 Cons:
