How Many Times Does a CTE run

Question

If I have a cte such as the below code. How many times does the table People get queried against? I was under the impression that it was only called 1 time and stored in memory but some of my queries I have been running seem to be running a lot longer than they should be. Which leads me to believe that it may be hitting the People Table 3 times.

with ctegeneric as (select person from people where person = 'dumb')
Select * from ctegeneric 
UNION ALL
Select * from ctegeneric 
UNION ALL
Select * from ctegeneric 

Answer 1

Putting aside the syntax was not correct (before the edit).

NO it does not get put in memory.

A proper example is a join. It will get call multiple times in a loop join. On an expensive CTE called multiple times then materialize to #temp is the way to go.

Answer 2

In SQL, a CTE can only be used/referenced in the (one) statement, where it is defined. And we know where statements end by using statement terminators (;).

SQL Server is forgiving and allows developers not to put these terminators (except for special cases where it does complain) but it's really good practice (and Microsoft recommends it) to use them after every statement. If you had them placed, it would be obvious that your code parses as 3 statements:

--- 1st statement starts ---
with ctegeneric as (select person from people where person = 'dumb') 
Select * from ctegeneric ;    -- and ends here

--- 2nd statement starts ---
Select * from ctegeneric ;    -- and ends here

--- 3rd statement starts ---
Select * from ctegeneric ;    -- and ends here

So your second and third statements should not actually work at all, and will return the error:

INVALID OBJECT NAME: ctegeneric

As soon as the statement where the CTE is made ends, you lose the ability to reference it again.

It's kind of like (this is not valid syntax either, just another way to think about CTEs):

WITH ctegeneric AS (SELECT person 
                    FROM people 
                    WHERE person = 'dumb')

BEGIN
Select * from ctegeneric ;
END

However, you can run the three selects with a UNION/UNION ALL:

WITH ctegeneric AS (SELECT person 
                    FROM people 
                    WHERE person = 'dumb')   
Select * from ctegeneric
UNION
Select * from ctegeneric
UNION
Select * from ctegeneric ;

Answer 3

The optimiser takes the submitted SQL and translates it into a set of actions called the query plan. In doing this it is free to arrange those actions in any sequence which is logically equivalent to the SQL. This may result in an object mentioned in the SQL being accessed once, many times, or not at all. This applies to objects in the CTE.

So, although it is commonly observed that objects mentioned in the CTE are accessed only once there is no guarantee that this will happen.

Like any other SQL statement the data processed through the CTE part is only in scope for the duration of that statement. If the same data is referenced in a subsequent statement it will be accessed again.

Answer 4

That code won't actually work. (op updated script after this answer was posted)

CTEs are effectively subqueries (that support recursion). They can only be referenced within the scope of that particular command. As such your first select * statement would work, the next two would error out not being able to find the CTE. In order for those to work you would need create another CTE for each to reference. And in that situation you would hit the people table 3 times, once for each query.

To improve this you could put your results into either a temp table, or table variable and then just query that.

Answer 5

In some cases, you may be able to avoid referencing the CTE more than one by using APPLY instead as Daniel Hutmacher explains in How to run your CTE just once, and re-use the output.