How does the WITH statement store the record set for the select query?

Question

Consider the following SQL (SQL Server 2008) statement:

WITH MyResult AS
(
  SELECT 
    Name,
    Row_ID AS ORD
  FROM Person Where Gender = @Gender
)
SELECT *
FROM MyResult
WHERE ORD > 5

Is MyResult stored in a temporary table in the tempdb? Or does it do something else?

We are optimising some queries and would lke to better understand WITH statements (internally) to help gauge performance etc.

Thanks

Answer 1

No, as per this MSDN article

...the CTE is a language-level construct—meaning that SQL Server does not internally create temp or virtual tables...

And also, as mentioned here:

A common table expression (CTE) can be thought of as a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. A CTE is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query.