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
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.
mou_inn
Participant
780 Points
955 Posts
regarding Common table expression CTE
Jan 10, 2012 12:51 PM|LINK
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
eralper
Contributor
6048 Points
971 Posts
Re: regarding Common table expression CTE
Jan 10, 2012 01:59 PM|LINK
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
SQL Server 2012
ksqcoder
Participant
1292 Points
207 Posts
Re: regarding Common table expression CTE
Jan 10, 2012 02:36 PM|LINK
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.