Is Alias and CTE are same?

Question

Hi All,

Is

select id from (select * from employee ) as t

is same as

;with temp as (select * from employee) select id from temp

?????

Both will return same result.But regarding performance oriented.

Answer 1

CTE's are for recursive queries.

Answer 2

Yes, in your example they are the same, you should view the execution plan to see any differences in how they work and hence performance. If you 'include execution plan' in SSMS and execute both queries in one batch, you will get a 'Query cost (relevant to the batch)' which will tell you which query performs better. I would guess that they are equivalent.

The advantage of a CTE (your second statement) over a derived table is that they can reference themselves and be used for recursion.