How CTE works in SQL Server - does it load whole table data in memory?

I want to understand If below query is going to give any performance issues.

Does a CTE in SQL Server read the whole data from TableA and loads it into memory, and then filters the data; or does it get only that data into memory which is filtered according to main query in outer part?

I read a blog post and want to confirm the same.

;`WITH test_CTE AS
(
    SELECT Id, COUNT(somecol) 
    FROM TableA
)
SELECT TableA.*, Id 
FROM TableA
LEFT JOIN test_CTE ON test_CTE.Id = TableA.Id
WHERE TableA.someotherCol = 'Filter'

How CTE works in SQL Server - does it load whole table data in memory?

0 Answers