Home Forums Article Discussions Article Discussions by Author Discuss content posted by Kev Riley Power and Deception of CTEs RE: Power and Deception of CTEs

  • December 1, 2008 at 12:46 pm

    #905872

    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...

    For me, the only gain that usually comes from a CTE (one or more) is that it makes the programming read a little better in a "Top Down" fashion instead of having to read "Bottom Up" with derived tables. Some like the ability to use recurrsive CTE's, but I've found that those are as bad or worse than cursors.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)