I wiould urge DBA's to be mindful of places where CTEs get used, but views would provide either better performance or a resource that could be used in several places.
Is there any information on the performance of a CTE vs. that of a view?
Hi all,
CTE 's are really powerful with their recursive usages.
I have also written an article on http://www.kodyaz.com/article.aspx?ArticleID=18 about Common Table Expressions.
You may check it for the examples...
Thanks.
Eralper
http://www.kodyaz.com
Hi Parker,
As far as I experienced, CTEs are using same execution plans as a normal view query.
So as you wrote I believe it makes no sense to use CTEs instead of views.
But if you are working with hierarchical data then CTEs are the address to look for a reliable solution
Hi Bryant,
I think that in sql statements where you can use CTEs as views, there exists ways of writing the CTE as an inner join or sub query.
Also, I tested with a few samples and saw that there is not a notiable increase in performance.
So I meant that the real reason or necessity for a CTE is not the usage of CTE instead of a view. You are right you should not need to create a view if it will be used once. But you can not also refer to a CTE for a second time. You can use it just after the definition of the CTE.
But even this usage is a plus and gives developers a flexiblity in their coding processes.
And I tried to mention that the real power of a CTE is visible when it is used as a recursive common table expression.
