Post reply

Power and Deception of CTEs

  • December 1, 2008 at 1:23 pm

    #905897

    Steve Jones - Editor (12/1/2008)

    Heh... thanks for the vote of confidence, but I learn something new about T-SQL everyday even if it's how to NOT do something. 😀

    I'd bet it's more often how not to do something than the other way around.

    Hopefully we'll get you up there at PASS next year doing some of the teaching.

    I second... let me go muy my tickets now!!!

  • December 1, 2008 at 11:27 pm

    #906044

    Ninja's_RGR'us (12/1/2008)

    Steve Jones - Editor (12/1/2008)

    Heh... thanks for the vote of confidence, but I learn something new about T-SQL everyday even if it's how to NOT do something. 😀

    I'd bet it's more often how not to do something than the other way around.

    Hopefully we'll get you up there at PASS next year doing some of the teaching.

    I second... let me go muy my tickets now!!!

    Awesome compliment's guys... I'm humbled. :blush: Thanks. I'd have to think of something I haven't already beat to death on the forums, though... heh... not much left.

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

  • December 2, 2008 at 4:03 am

    #906122

    If it's worth beating to death on the forums, it means that th ecommunity needs to hear it... people wanting to hear what you have to say or needing to hear it will attend... you don't have to try to please everybody out there, it's not possible (God knows I tried that one!).

  • December 2, 2008 at 4:10 am

    #906125

    Good point Steve, I was asking just yesterday about the left join vs NOT Exists, and well Gail gave me a very good visible answer and well ... like you say just cause it looks good doesn't mean it is good 🙂

    ----------------------------------------------
    Try to learn something about everything and everything about something. - Thomas Henry Huxley

    :w00t:
    Posting Best Practices[/url]
    Numbers / Tally Tables[/url]

    SQL-4-Life

  • December 2, 2008 at 4:15 am

    #906126

    Nice article ...

  • December 3, 2008 at 9:37 am

    #906991

    Christopher Stobbs (12/2/2008)

    Good point Steve, I was asking just yesterday about the left join vs NOT Exists, and well Gail gave me a very good visible answer and well ... like you say just cause it looks good doesn't mean it is good 🙂

    Can someone please post the link to the thread Steve and Chris was talking about? Thanks.

    Paul DB

  • December 3, 2008 at 4:07 pm

    #907238

    Ah, was afraid you would ask that. I'll look for it.

  • December 3, 2008 at 4:08 pm

    #907239

    tada - http://www.sqlservercentral.com/Forums/Topic611416-338-1.aspx

  • December 5, 2008 at 5:50 pm

    #908730

    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/

  • December 6, 2008 at 8:18 am

    #908810

    timothyawiseman (12/5/2008)

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

    I'm definitely for that and glad to see anyone concerned about readability for a change... but even CTE's won't make readable code without a bit of concern for formatting and commenting. Heh... I had one guy at my previous job that thought it was a good formatting choice to write each query on a single line and all lower case.

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

  • December 6, 2008 at 2:38 pm

    #908842

    Jeff Moden (12/6/2008)

    timothyawiseman (12/5/2008)

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

    I'm definitely for that and glad to see anyone concerned about readability for a change... but even CTE's won't make readable code without a bit of concern for formatting and commenting. Heh... I had one guy at my previous job that thought it was a good formatting choice to write each query on a single line and all lower case.

    You have a point. But, in my opinion, a well formatted CTE will be easier to read and understand than even a well formatted derived table inside some other portion of the command.

    ---
    Timothy A Wiseman
    SQL Blog: http://timothyawiseman.wordpress.com/

  • December 6, 2008 at 3:35 pm

    #908847

    So application developer A writes the CTE -- or the derived table -- then application developer B writes it slightly differently a month later in another application. Modularity? No, maintenance nightmare. There should be a DBA writing views, verifying with business analysts that the views select exactly what is expected, documenting the views, and reviewing the SQL written by the application developers to make sure they use standard views -- instead of developing as if their programming creativity is what it's about.

    _________________
    "Look, those sheep have been shorn."
    data analyst replies, "On the sides that we can see.."

  • December 6, 2008 at 3:38 pm

    #908849

    timothyawiseman (12/6/2008)

    Jeff Moden (12/6/2008)

    timothyawiseman (12/5/2008)

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

    I'm definitely for that and glad to see anyone concerned about readability for a change... but even CTE's won't make readable code without a bit of concern for formatting and commenting. Heh... I had one guy at my previous job that thought it was a good formatting choice to write each query on a single line and all lower case.

    You have a point. But, in my opinion, a well formatted CTE will be easier to read and understand than even a well formatted derived table inside some other portion of the command.

    Heh... AGREED! Ephasis on "well formatted". You and I are saying the same thing!

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

Viewing 13 posts - 16 through 27 (of 27 total)

You must be logged in to reply to this topic. Login to reply