Published on

October 30, 2009

Exploring Common Table Expressions in SQL Server

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.

Click to rate this post!
[Total: 0 Average: 0]

Let's work together

Send us a message or book free introductory meeting with us using button below.