SQL Server CTEs Don’t Always Execute In The Order You Write Them

Mythology

I’ve heard many times incorrectly over the years that CTEs somehow materialize data.

But a new one to me was that CTEs execute procedurally, and you could use that to influence plan shapes by always doing certain things first.

Unfortunately, that’s not true of them either, even when you use TOP.

Meaningful Life

Here’s the first example. Take some note of the order the CTEs are written and joined in, and the tables they touch.

Outside of the CTEs, there’s a join to a table not even in a CTE here.

WITH cte_1 AS 
(
SELECT u.Id
FROM dbo.Users AS u
WHERE u.Reputation = 1
),
     cte_2 AS
(
SELECT p.OwnerUserId, p.Id
FROM dbo.Posts AS p
WHERE p.OwnerUserId = 22656
),   cte_3 AS 
(
SELECT v.PostId
FROM dbo.Votes AS v --WITH(INDEX = three)
WHERE v.VoteTypeId = 4
)
SELECT COUNT(*)
FROM cte_1
JOIN cte_2
    ON cte_2.OwnerUserId = cte_1.Id
JOIN cte_3 
    ON cte_3.PostId = cte_2.Id
JOIN dbo.Comments AS c
    ON c.UserId = cte_1.Id;

The plan for it looks like this:

Not even close to happening in the order we wrote things in.

Darn that optimizer.

Machanically Speaking

If we use a TOP in each CTE, that doesn’t help us either.

WITH cte_1 AS 
(
SELECT TOP (2147483647) u.Id
FROM dbo.Users AS u
WHERE u.Reputation = 1
),
     cte_2 AS
(
SELECT TOP (2147483647) p.OwnerUserId, p.Id
FROM dbo.Posts AS p
WHERE p.OwnerUserId = 22656
),   cte_3 AS 
(
SELECT TOP (2147483647) v.PostId
FROM dbo.Votes AS v
WHERE v.VoteTypeId = 4
)
SELECT COUNT(*)
FROM cte_1
JOIN cte_2
    ON cte_2.OwnerUserId = cte_1.Id
JOIN cte_3 
    ON cte_3.PostId = cte_2.Id
JOIN dbo.Comments AS c
    ON c.UserId = cte_1.Id;

Tables get touched in the same order, but the plan takes an ugly turn:

Dis-spells

CTEs have no magic powers. They don’t boss the optimizer around, they don’t materialize, and they don’t fence optimization.

If you’re gonna start stacking these things together, make sure you’re doing it for a good reason.

And if you tell me it’s to make code more readable, I know you’re messing with me.

Thanks for reading!

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.