Troubleshoot Chained SQL Server CTE

By: Jared Westover | Updated: 2024-10-16 | Comments | Related: > Common Table Expressions

Problem

I started using Common Table Expressions (CTEs) in Oracle before Microsoft SQL Server and found them easier to read than a derived table. Something about reading from the top down just clicked in my brain. As the years have passed, I'm less of a fan for one reason: troubleshooting a chained CTE with several links is hard—especially when it's someone else's code. Even the ones I wrote years ago fill me with unease when something breaks. Is there an easier way to break down each link in the chain when things go wrong?

Solution

This SQL tutorial will explore how chained CTEs might make troubleshooting code harder. The process can be further complicated if your developers like using local variables. Next, we'll look at using a temporary table as a substitute. But what are the trade-offs of using a temporary table versus a CTE? Also, should you stop using CTEs altogether? These are a few of the questions I'll address throughout this article.

Defining Terms

Before diving in, let's define each element we'll examine in this article. Since countless articles have already been written about CTEs, my intent is not to write another one. Instead, I want to highlight the differences between a derived table and a subquery since those terms are often confused. Below, I've defined all three of them.

SQL Common Table Expression

A Common Table Expression (CTE) is like a derived table but is defined top-down. It's often described as a temporary result set that's only accessible within the query where it's defined. Unlike a temporary table, SQL Server doesn't store the results, so you can't reuse them outside the query.

Below is a simple CTE example. This query retrieves the salesperson who made the most recent sale of each unique product from product category 2.

-- mssqltips.com
;WITH cte
AS (SELECT SalesPersonId,
           ProductId,
           ROW_NUMBER() OVER (PARTITION BY ProductId ORDER BY TransactionDate DESC) rn
    FROM dbo.SalesTransactions
    WHERE ProductCategoryId = 2
   )
SELECT SalesPersonId,
       ProductId
FROM cte
WHERE rn = 1;

It seems that no matter the query, I end up incorporating a CTE into it, especially when using windowing functions.

Derived Table

A derived table is also like a CTE, but you place it in the FROM clause of a query. Some refer to derived tables as subqueries, though that term can be confusing. I suppose you could say they are a type of subquery. A derived table only exists for the duration of a single query, just like a CTE. The query below shows a derived table in action.

-- mssqltips.com
SELECT SalesPersonId,
       ProductId
FROM
(
    SELECT SalesPersonId,
           ProductId,
           ROW_NUMBER() OVER (PARTITION BY ProductId ORDER BY TransactionDate DESC) rn
    FROM dbo.SalesTransactions
    WHERE ProductCategoryId = 2
) AS dt
WHERE rn = 1;

If you execute these queries, SQL Server uses the same execution plan, and the logical reads are identical.

Subquery

SQL Server offers two types of subqueries: correlated and non-correlated. Here, I'll focus on the non-correlated type. A non-correlated subquery runs independently of the main query. For example, you might use a subquery if you want to return the sum of all sales for a specific column. You'll typically find a subquery in the SELECT clause as a column expression or in the WHERE clause as a predicate. Below is a simple example.

-- mssqltips.com
SELECT SalesPersonId,
       ProductId,
       (
           SELECT COUNT(1) FROM dbo.SalesTransactions WHERE ProductCategoryId = 2
       ) AS Category2Count
FROM dbo.SalesTransactions;

With the terms defined, let's explore why you might want to use a chained CTE instead of a nested derived table.

Nested Derived Tables

Why would you need to create a nested derived table? Let me offer two reasons. First, nesting becomes necessary when you need to perform multiple transformations on a result set. Second, when you want to execute the code step by step. Perhaps you could even combine three derived tables into one, but steps make following the code logic easier.

Below is an example of a nested derived table. I'm only going two levels deep in this example, but I'm sure you've seen much worse. This query finds the top three most recent sales of products from category 2 where the sales amount is greater than or equal to the average.

-- mssqltips.com
SELECT TOP (3)
    dt2.SalesPersonId,
    dt2.ProductId,
    dt2.SaleAmount
FROM
(
    SELECT dt1.SalesPersonId,
           dt1.ProductId,
           dt1.SaleAmount,
           dt1.AverageSalesAmount,
           ROW_NUMBER() OVER (ORDER BY dt1.TransactionDate DESC) AS rn
    FROM
    (
        SELECT SalesPersonId,
               ProductId,
               TransactionDate,
               AVG(SaleAmount) OVER (ORDER BY (SELECT NULL)) AS AverageSalesAmount,
               SaleAmount
        FROM dbo.SalesTransactions
        WHERE ProductCategoryId = 2
    ) AS dt1
) AS dt2
WHERE dt2.SaleAmount >= dt2.AverageSalesAmount
ORDER BY dt2.SaleAmount DESC,
         dt2.rn ASC;

Chained CTEs

Like the nested derived table, a chained CTE allows you to perform multiple transformations step-by-step. For many, reading a chained CTE is easier since the syntax flows vertically at the top of the query rather than being nested within. Below is the same query, but it uses a chained CTE.

-- mssqltips.com
;WITH cte1
AS (SELECT SalesPersonId,
           ProductId,
           TransactionDate,
           AVG(SaleAmount) OVER (ORDER BY (SELECT NULL)) AS AverageSalesAmount,
           SaleAmount
    FROM dbo.SalesTransactions
    WHERE ProductCategoryId = 2
   ),
      cte2
AS (SELECT SalesPersonId,
           ProductId,
           ROW_NUMBER() OVER (ORDER BY TransactionDate DESC) AS rn,
           AverageSalesAmount,
           SaleAmount
    FROM cte1
   )
SELECT TOP (3)
    SalesPersonId,
    ProductId,
    SaleAmount
FROM cte2
WHERE SaleAmount >= AverageSalesAmount
ORDER BY SaleAmount DESC,
         rn ASC;

Some people prefer vanilla over chocolate ice cream; others even enjoy strawberry. Ultimately, the choice comes down to personal preference. From a readability standpoint, I prefer chained CTEs. However, I've worked with people who can't stand CTEs—I'm not quite sure why!

Troubleshooting Chained CTEs

One thing I detest about a chained CTE is troubleshooting a step when something is off. The first CTE is rarely the issue, but things get hard when it's the fifth or sixth link in the chain. Imagine someone telling us the results are wrong—they expect more rows. My instinct is to run the first CTE, but I don't see any issues. Our next step is to break out each of the CTEs using comments so I can check where the problem exists in the chain.

-- mssqltips.com
;WITH cte1
AS (SELECT SalesPersonId,
           ProductId,
           TransactionDate,
           AVG(SaleAmount) OVER (ORDER BY (SELECT NULL)) AS AverageSalesAmount,
           SaleAmount
    FROM dbo.SalesTransactions
    WHERE ProductCategoryId = 2
   )--,
      --cte2
--AS (
SELECT SalesPersonId,
           ProductId,
           ROW_NUMBER() OVER (ORDER BY TransactionDate DESC) AS rn,
           AverageSalesAmount,
           SaleAmount
    FROM cte1
--   )
--SELECT TOP (3)
--    SalesPersonId,
--    ProductId,
--    SaleAmount
--FROM cte2
--WHERE SaleAmount >= AverageSalesAmount
--ORDER BY SaleAmount DESC,
--         rn ASC;

I dread breaking the query down with comments. There must be an easier way to troubleshoot this code.

Temporary Tables

SQL Server creates temporary tables in tempdb, and they are commonly used to store intermediate results. One advantage of temporary tables is that SQL automatically generates statistics so that you might get better execution plans than a table variable. Let's see how we can rewrite the CTE above using temporary tables.

DROP TABLE IF EXISTS #temp1
DROP TABLE IF EXISTS #temp2

SELECT SalesPersonId,
       ProductId,
       TransactionDate,
       AVG(SaleAmount) OVER (ORDER BY (SELECT NULL)) AS AverageSalesAmount,
       SaleAmount
INTO #temp1
FROM dbo.SalesTransactions
WHERE ProductCategoryId = 2;

SELECT SalesPersonId,
       ProductId,
       ROW_NUMBER() OVER (ORDER BY TransactionDate DESC) AS rn,
       AverageSalesAmount,
       SaleAmount
INTO #temp2
FROM #temp1;

SELECT TOP (3)
    SalesPersonId,
    ProductId,
    SaleAmount
FROM #temp2
WHERE SaleAmount >= AverageSalesAmount
ORDER BY SaleAmount DESC,
         rn ASC;

We still use the step-based method for our transformations, but here, we can simply query each temp table to see the results up to a specific step. This approach is much easier for me to troubleshoot.

Downside to Temporary Tables

As Thomas Sowell said, "There are no solutions. There are only trade-offs." With that in mind, here are some downsides to replacing chained CTEs with temporary tables:

Additional resource usage: Creating temporary objects, including tables and statistics, requires extra time, memory, and storage.
Lack of indexes: For example, in the code above, if covering indexes exist later in the chain, you would need to recreate them for the temporary tables.
Manually CREATE and DROP temporary tables: If you don't drop them and leave the session open, it can lead to clutter.

With the above points in mind, don't take this article as a recommendation to rewrite all your chained CTEs as temporary tables. If you do, remember—don't blame me.

Help Converting

You might think, "I don't want to rewrite everything as temporary tables," and I wouldn't blame you. However, this is one area where I believe an AI, like ChatGPT or Copilot, can help. Let's see how this works in the real world. Below, I've added the prompt and code generated.

-- Prompt: Please take the code below and convert it to temporary tables. Do not explicitly create the tables.

-- Step 1: Insert data into a temporary table (equivalent to cte1)
SELECT SalesPersonId,
       ProductId,
       TransactionDate,
       AVG(SaleAmount) OVER (ORDER BY (SELECT NULL)) AS AverageSalesAmount,
       SaleAmount
INTO #TempTable1
FROM dbo.SalesTransactions
WHERE ProductCategoryId = 2;

-- Step 2: Insert data into a second temporary table (equivalent to cte2)
SELECT SalesPersonId,
       ProductId,
       ROW_NUMBER() OVER (ORDER BY TransactionDate DESC) AS rn,
       AverageSalesAmount,
       SaleAmount
INTO #TempTable2
FROM #TempTable1;

-- Step 3: Select statement with the final result
SELECT TOP (3)
    SalesPersonId, -- column names
    ProductId,
    SaleAmount
FROM #TempTable2
WHERE SaleAmount >= AverageSalesAmount
ORDER BY SaleAmount DESC,
         rn ASC;

-- Optional: Drop the temporary tables if needed
-- DROP TABLE #TempTable1, #TempTable2;

Not too shabby! Instead of you or the developer writing this code as a temporary table, you can use AI to convert it.

Summary

Chained CTEs are a helpful tool in SQL Server and, for some, much easier to read than nested derived tables. With a chained CTE, you don't need to worry about including additional indexes as you might with the temporary table approach. However, when a chain grows to several links, they can be hard to debug. Transforming them into separate steps with temporary tables can simplify troubleshooting.

I'd love to hear from you. What methods do you use when trying to troubleshoot long-chained CTEs? Feel free to share tips in the comments below.

Next Steps

Would you like to learn more about the different types of temporary tables available in SQL Server? Check out my article, 'Mastering SQL Server Local and Global Temporary Tables,' to help you get started.
For a detailed comparison of the performance differences between CTEs and temp tables, look at Ben Snaidero's article, 'SQL Server CTE vs Temp Table vs Table Variable Performance Test.'
Not sure whether to use a view, CTE, or subquery? Eric Blinn compares these options and more in the article, 'Choose Between SQL Server Subquery T-SQL Code.'
Check out these additional tips:
- Exploring a Recursive CTE in SQL Server with Examples
- SQL Common Table Expressions on MSSQLTips.com
- Recursive Queries
- INNER JOIN, LEFT JOIN and RIGHT JOIN
- UNION vs. UNION ALL

About the author

Jared Westover is a passionate technology specialist at Crowe, helping to build data solutions. For the past two decades, he has focused on SQL Server, Azure, Power BI, and Oracle. Besides writing for MSSQLTips.com, he has published 12 Pluralsight courses about SQL Server and Reporting Services. Jared enjoys reading and spending time with his wife and three sons when he's not trying to make queries go faster.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2024-10-16