First load table in CTE and then query the CTE?

A colleague of mine writes views like this:

Create view myview as 

    with CTE as (
    select * from tableA)
    ,CTE2 as (
    select * from TableB)
    
    Select * from CTE
    UNION ALL
    Select * from CTE2

He says that his professor taught him this because ‘things will be loaded in RAM and it is a lot faster’. Unfortunately the professor is no longer able to explain this. I think the following works at least as good and avoids unnecessary complexity in the code.

Create view myview as
    Select * from TableA
    UNION ALL
    Select * from TableB

Am I missing something? I have known that prof and he is quite good so I am assuming he had good reasons to teach his students to write all queries like this. Am I missing something here? What could be good reasons to write queries that pass all data through CTE without transforming/filtering it?

Answer

The reason is that the professor is simply wrong. SQL Server basically copies the code from the CTE in to the final query. This allows the optimizer to optimize the entire query and is usually a good approach.

Some databases materialize CTEs — either all the time or sometimes. In those databases, a CTE references more than once might be more efficient. Or it might not be — important optimization information is lost (such as indexes and statistics).

There is no reason to have these CTEs in this query for SQL Server or for any other database.

CTEs are very useful in SQL Server for structuring queries, for reducing code duplication, and for implementing recursive functionality. However, they do not provide improvements in perfromance.