How Many Times Does a CTE run 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
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.