I agree 100% There are a lot of constructs available in SQL Server. A lot of developers don't really understand the importance of using the right construct to complete a task. Understanding the limitations of each construct is very important.
As a rule I try to get an understanding of the scope of data that I am working with. Once I have a good understanding with the scope of data I will decide to use one of a number of different constructs available matching the right construct with the scope of data.
Another thing I look at is where the code that I'm writing is going to be used. If it is going to be public facing requiring user interaction waiting for the code to perform its job then I will be sure to design & test multiple constructs to insure the ultimate performance.
If I'm writing ETL code with say less than 100k rows of data to process I might consider things like CTEs or table variables for my processing.
If I'm working with very large data sets then I shift my design, once again looking for the best overall performance using the right constructs.
So the CTE is a tool. Used correctly, within the right scope of data, they perform very well. I find the construct much easier to read, which is one of my biggest concerns.
Have a great day.
Kurt Zimmerman
g.britton (3/19/2015)
Alan.B (3/18/2015)
arthur.bekker (3/18/2015)
I think anyone who wants to use CTEs should read this article http://sqlblogcasts.com/blogs/tonyrogerson/archive/2008/05/17/non-recursive-common-table-expressions-performance-sucks-1-cte-self-join-cte-sub-query-inline-expansion.aspx....I showed the Execution Plan to this guy and he said that he wanted to "concrete" his SQL statements. I'm guessing there's plenty of concrete in his head as he couldn't understand what the Execution Plan was telling him.
Not to beat a dead horse here but if your point is that CTEs are inherently bad then you are 100% wrong. I don't care what the internet told you, if you are saying that CTEs are bad then you are wrong. They might not be your personal preference but there is nothing wrong with CTEs.
There are ways to write bad queries. There are ways to write bad queries that involve CTEs. But, if you know what you are doing, then CTEs are absolutely fine and are an invaluable tool for any person who develops SQL for a living. Period.
Agree 100%! Though, if you think you need to write a recursive CTE, think long and hard about alternatives, for performance reasons. One of the most cited examples is a recursive descent of the manager/employee tree in an organization. However, many organizations are not that deep. Maybe less than 10 levels. You can write a query using a bunch of self-joins instead that will do the same thing. It may be ugly but the execution plan will be set-based and not RBAR. The performance difference will be definitely measurable and often huge.
Kurt W. Zimmerman
SR DBA
Lefrak Organization
New York, NY
http://www.linkedin.com/in/kurtwzimmerman