Common Table Expressions (CTEs) are a powerful feature in SQL Server that can simplify your T-SQL queries and improve performance. Introduced in SQL Server 2005, CTEs allow you to separate part of your T-SQL logic from your main query, eliminating the need for views, correlated subqueries, or temporary tables.
The syntax for creating a CTE is straightforward. Start with the keyword “WITH”, followed by a name for your CTE, and then use the keyword “AS” and a set of parentheses to define the CTE. Inside the parentheses, you can write a valid SELECT query. Once defined, you can use the CTE in your main query just as if it were a table or view.
Let’s take a look at some examples to understand how CTEs can be used effectively.
Example 1: Using CTEs to Join and Retrieve Data
In this example, we have a table called “Employee” and another table called “Contact” in the AdventureWorks database. We want to retrieve the details of employees along with their manager’s information. Instead of using multiple subqueries or temporary tables, we can use a CTE to simplify the query:
WITH emp AS ( SELECT EmployeeID, FirstName, LastName, E.Title, ManagerID FROM HumanResources.Employee AS E INNER JOIN Person.Contact AS C ON E.ContactID = C.ContactID ) SELECT A.EmployeeID, A.FirstName, A.LastName, A.Title, A.ManagerID, B.FirstName AS MgrFirstName, B.LastName AS MgrLastName, B.Title AS MgrTitle FROM emp AS A INNER JOIN emp AS B ON A.ManagerID = B.EmployeeID;
This query demonstrates how the CTE “emp” is used multiple times within the main query to join the employees with their respective managers.
Example 2: Fixing Poorly Performing Correlated Subqueries
Correlated subqueries can sometimes lead to poor performance. In this example, we have a query that uses correlated subqueries to calculate various aggregate values for each customer. We can rewrite the query using a CTE to improve performance:
WITH csales AS ( SELECT COUNT(*) AS CountOfSales, AVG(TotalDue) AS AvgSale, MIN(TotalDue) AS LowestSale, MAX(TotalDue) AS HighestSale, CustomerID FROM Sales.SalesOrderHeader GROUP BY CustomerID ) SELECT SalesOrderID, S.CustomerID, CountOfSales, AvgSale, LowestSale, HighestSale FROM Sales.SalesOrderHeader AS S INNER JOIN csales ON S.CustomerID = csales.CustomerID;
By using a CTE, we can calculate the aggregate values once and join them with the main query, resulting in improved performance compared to the correlated subquery approach.
Example 3: Combining ROW_NUMBER Function with a CTE
The ROW_NUMBER function is a useful feature in SQL Server for adding row numbers to query results. However, it cannot be directly used in the WHERE clause. In this example, we combine the ROW_NUMBER function with a CTE to overcome this limitation:
WITH emp AS ( SELECT ROW_NUMBER() OVER(ORDER BY LastName, FirstName) AS RowNumber, EmployeeID, FirstName, LastName FROM HumanResources.Employee AS e INNER JOIN Person.Contact AS c ON e.ContactID = c.ContactID ) SELECT RowNumber, EmployeeID, FirstName, LastName FROM emp WHERE RowNumber BETWEEN 11 AND 20 ORDER BY RowNumber;
By using a CTE, we can assign row numbers to the result set and then filter the rows based on the row number range.
Example 4: Joining CTEs
CTEs can also be joined together to build more complex queries. In this example, we have two CTEs: “maxDate” and “orders”. The “maxDate” CTE retrieves the maximum order date for each customer, and the “orders” CTE joins the first CTE to the Sales.SalesOrderHeader table to retrieve the most recent order for each customer:
WITH maxDate AS ( SELECT MAX(OrderDate) AS MaxOrderDate, CustomerID FROM Sales.SalesOrderHeader GROUP BY CustomerID ), orders AS ( SELECT SalesOrderID, soh.CustomerID, OrderDate FROM Sales.SalesOrderHeader AS soh INNER JOIN maxDate ON soh.CustomerID = maxDate.CustomerID AND soh.OrderDate = maxDate.MaxOrderDate ) SELECT CustomerID, ProductID, sod.SalesOrderID, OrderDate FROM orders INNER JOIN Sales.SalesOrderDetail AS sod ON sod.SalesOrderID = orders.salesOrderID;
This query demonstrates how one CTE can be based on another CTE, allowing us to retrieve the desired information efficiently.
Common Table Expressions are a valuable tool in SQL Server that can simplify complex queries, improve performance, and enhance code readability. By understanding the syntax and various use cases, you can leverage CTEs to write more efficient and maintainable T-SQL code.
Thank you for reading! Stay tuned for more SQL Server tips and tricks.