You may already know that common table expressions, like views, don’t behave like regular tables. They’re a way to make your query more readable by allowing you to write a complex SQL expression just once, rather than repeating it all over your statement or view. This makes reading, understanding and future refactoring of your code a little less painful.

But they’re no magic bullet, and you may end up with some unexpected execution plans.

Straight to demo

The example queries in this post run in the StackOverflow database, which Brent Ozar has made available for some time now. If you want to follow along, I would also add the following index for good measure:

CREATE INDEX IX_Posts_Type_Owner
    ON dbo.Posts
        (PostTypeId, OwnerUserId)
    INCLUDE (ViewCount, CommentCount, FavoriteCount);

The problem

If you’re a long-time reader of the blog, you may already know that this query actually executes the code in the CTE four times.

WITH cte AS (
    SELECT u.DisplayName, u.Reputation,
           SUM(p.ViewCount) AS ViewCount,
           SUM(p.CommentCount) AS CommentCount,
           SUM(p.FavoriteCount) AS FavoriteCount
    FROM dbo.Users AS u
    LEFT JOIN dbo.Posts AS p ON p.OwnerUserId=u.Id AND p.PostTypeId IN (1, 2)
    GROUP BY u.DisplayName, u.Reputation)

--- 1
SELECT DisplayName, 'Reputation' AS Metric, Reputation AS [Value]
FROM cte
WHERE Reputation>0

UNION ALL

--- 2
SELECT DisplayName, 'Views' AS Metric, ViewCount AS [Value]
FROM cte
WHERE ViewCount>0

UNION ALL

--- 3
SELECT DisplayName, 'Comments' AS Metric, CommentCount AS [Value]
FROM cte
WHERE CommentCount>0

UNION ALL

--- 4
SELECT DisplayName, 'Favorited' AS Metric, FavoriteCount AS [Value]
FROM cte
WHERE FavoriteCount>0;

Under the hood, SQL Server “expands” the common table expression, so the query becomes something like this:

--- 1
SELECT DisplayName, 'Reputation' AS Metric, Reputation AS [Value]
FROM (
    SELECT u.DisplayName, u.Reputation
    FROM dbo.Users AS u
    GROUP BY u.DisplayName, u.Reputation) AS cte
WHERE Reputation>0

UNION ALL

--- 2
SELECT DisplayName, 'Views' AS Metric, ViewCount AS [Value]
FROM (
    SELECT u.DisplayName, SUM(p.ViewCount) AS ViewCount
    FROM dbo.Users AS u
    LEFT JOIN dbo.Posts AS p ON p.OwnerUserId=u.Id AND p.PostTypeId IN (1, 2)
    GROUP BY u.DisplayName) AS cte
WHERE ViewCount>0

UNION ALL

--- 3
SELECT DisplayName, 'Comments' AS Metric, CommentCount AS [Value]
FROM (
    SELECT u.DisplayName, SUM(p.CommentCount) AS CommentCount
    FROM dbo.Users AS u
    LEFT JOIN dbo.Posts AS p ON p.OwnerUserId=u.Id AND p.PostTypeId IN (1, 2)
    GROUP BY u.DisplayName) AS cte
WHERE CommentCount>0

UNION ALL

--- 4
SELECT DisplayName, 'Favorited' AS Metric, FavoriteCount AS [Value]
FROM (
    SELECT u.DisplayName, SUM(p.FavoriteCount) AS FavoriteCount
    FROM dbo.Users AS u
    LEFT JOIN dbo.Posts AS p ON p.OwnerUserId=u.Id AND p.PostTypeId IN (1, 2)
    GROUP BY u.DisplayName) AS cte
WHERE FavoriteCount>0;

These two queries generate identical execution plans, but the one with the CTE is much, much more readable.

You can tell by the plan why this is an inefficient query: the SQL expression in the common table expression is executed once for every time that it’s referenced in the code.

Better living through CROSS APPLY

You could store the results of the CTE in a temp table, but where’s the fun in that? Instead, why not use the CTE once, and then return four rows for each row that the CTE spits out? That’s exactly what CROSS APPLY does.

WITH cte AS (
    SELECT u.DisplayName, u.Reputation,
           SUM(p.ViewCount) AS ViewCount,
           SUM(p.CommentCount) AS CommentCount,
           SUM(p.FavoriteCount) AS FavoriteCount
    FROM dbo.Users AS u
    LEFT JOIN dbo.Posts AS p ON p.OwnerUserId=u.Id AND p.PostTypeId IN (1, 2)
    GROUP BY u.DisplayName, u.Reputation)

SELECT DisplayName, x.Metric, x.[Value]
FROM cte
CROSS APPLY (
    --- 1
    SELECT 'Reputation' AS Metric, cte.Reputation AS [Value] WHERE cte.Reputation>0

    UNION ALL

    --- 2
    SELECT 'Views' AS Metric, cte.ViewCount AS [Value] WHERE cte.ViewCount>0

    UNION ALL

    --- 3
    SELECT 'Comments' AS Metric, cte.CommentCount AS [Value] WHERE cte.CommentCount>0

    UNION ALL

    --- 4
    SELECT 'Favorited' AS Metric, cte.FavoriteCount AS [Value] WHERE cte.FavoriteCount>0
    ) AS x;

Think of CROSS APPLY (and it’s baby sibling OUTER APPLY) as a way to do a “for-each” construct, but in T-SQL. “For each row generated above, execute and join this.“

Now, you can now see that the execution plan only scans the Posts and Users tables once each, instead of once for every UNION ALL set.

And the statistics add up: The original query performed around 82,000 logical reads on dbo.Posts and 178,000 logical reads on dbo.Users.

Our new-and-improved CROSS APPLY query only needs 27,200 logical reads on dbo.Posts and 44,500 reads on dbo.Users to accomplish the same results. These are single-thread results, but they scale when you run parallel threads.

Just one more little tweak

We can simplify the query just a little bit more. By moving the WHERE clauses out of the CROSS APPLY, we can turn the SELECT … UNION ALL.. pattern into a VALUES construct:

WITH cte AS (
    SELECT u.DisplayName, u.Reputation,
           SUM(p.ViewCount) AS ViewCount,
           SUM(p.CommentCount) AS CommentCount,
           SUM(p.FavoriteCount) AS FavoriteCount
    FROM dbo.Users AS u
    LEFT JOIN dbo.Posts AS p ON p.OwnerUserId=u.Id AND p.PostTypeId IN (1, 2)
    GROUP BY u.DisplayName, u.Reputation)

SELECT DisplayName, x.Metric, x.[Value]
FROM cte
CROSS APPLY (
    VALUES ('Reputation', cte.Reputation),    --- 1
           ('Views',      cte.ViewCount),     --- 2
           ('Comments',   cte.CommentCount),  --- 3
           ('Favorited',  cte.FavoriteCount)  --- 4
    ) AS x(Metric, [Value])
WHERE x.[Value]>0;

This reduces the amount of work the Nested Loops operator needs to do, and dramatically simplifies the plan:

The IO statistics are about the same for this query, but the CPU time is about 10% less, because we’ve eliminated a few Compute Scalar and Filter operators from the plan.