Last post Jan 10, 2012 02:36 PM by ksqcoder
Jan 10, 2012 12:51 PM|mou_inn|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
Jan 10, 2012 01:59 PM|eralper|LINK
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
Jan 10, 2012 02:36 PM|ksqcoder|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.