regarding Common table expression CTE

what is CTE and what is the advantage. is there any advantage regarding performance of using CTE. it looks it is like temporary view.

WITH MyData AS
(
select Specialist,jid from bbajobs
)

select * from MyData

please discuss about CTE in detail

Re: regarding Common table expression CTE

Hello mou_inn,

For me the most outstandind advantage is you can query hierarchical structures in SQL using CTE expressions. Developers can build recursive sql queries using CTE in SQL Server . Please check the reference for an example

Also you do not need to define temporary tables. You can create multiple CTE's in a SQL query and refer to a CTE within the same query more than once.

I'm not sure it has a disadvantage when compared with creating temp tables or using sub-selects

Re: regarding Common table expression CTE

You are correct a CTE is like a temporary view. However a CTE is often smaller than a view so they are better to use in a stored procedure rather than creating a proper view for a small set of data. As for performance, that can be debated as to what is really faster but I have never had a performance issue with them.

I like using CTEs mainly because they are easy to write and maintain. Also when other developers on my team write sprocs using CTEs they are pretty straight forward to read which helps when debugging something new I did not write myself (or I wrote months or years ago and have forgotten all about). Another good point is that they are fairly easy to use recursion with, which can be quite helpful from time to time. This link is a bit older but still relevant and gives a good overview of CTEs.