G Bryant McClellan (12/1/2008)
Jeff Moden (12/1/2008)
Good article... but, because of the title, I was actually expecting to see something about a problem or technique with CTE's that couldn't be done using other forms of code. The article is really about how an index can help any query be it a CTE, Derived Table, View, etc.Jeff,
I was also thrown by the title although 'deception' gave me a clue.
On the other hand, thanks to Kev for pointing out what should have been obvious...the use of indexes on columns commonly used in WHERE and ORDER BY clauses. I must admit that it gave me ideas on some inherited problem code that is built with CTE for no reason I can discern. The CTE is (to me) no gain over standard SQL but potentially a gauze curtain covering up an underlying indexing issue. And I would venture that it was authored by a procedural programmer...
CTEs really bring two things tot he table that subqueries/derived tables do not. One is the ability to use recursion. It is definitely much maligned, and should be used with care, but it does have its uses.
More significantly, it can improve readability. It helps make it easier to read from top to bottom and it can move complexity from the "From" clause to a less cluttered area. To slightly modify a quote from Larry Wall, "SQL is designed to give you several ways to do anything, so consider picking the most readable one."
---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/