T-SQL Syntax Gotchas

  • Sean Pearce

    SSCoach

    Points: 15750

    Comments posted to this topic are about the item T-SQL Syntax Gotchas

    The SQL Guy @ blogspot[/url]

    @SeanPearceSQL

    About Me[/url]

  • Hardy21

    SSCrazy Eights

    Points: 9708

    Nice One.

    I did quick search for COMPUTE. 😉

    Thanks

  • Iulian -207023

    SSCertifiable

    Points: 7509

    Thank you for the question.

  • Koen Verbeeck

    SSC Guru

    Points: 258965

    Great question, thanks!

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • twin.devil

    SSC-Insane

    Points: 22208

    Nice question, Thanks for sharing

  • Zagyg

    SSCrazy

    Points: 2029

    Good question, enjoyed puzzling through this one (even if I did get it wrong through poor recall) :ermm:

    I especially like how question 4, even if it did work, is conceptually the "wrong way around" and won't try to create the table unless it already exists! :hehe: Nice touch on the "reliable internets" theme.

  • This was removed by the editor as SPAM

  • Ed Wagner

    SSC Guru

    Points: 286982

    Thank you for a good question. Had to take a second look at some of them and it helped my brain wake up, which I desperately need this morning.

  • Toreador

    SSChampion

    Points: 11256

    Being pedantic, only 3 of those statements return syntax errors...

  • TomThomson

    SSC Guru

    Points: 104773

    Nice question.

    I hadn't seen Itzik Ben-Gan's "take control of joins" before. It's an interesting note. But it's rather flawed, and it's going to confuse people and turn them against a really good method of writing joins.

    I can't agree with him that "the first technique is much more readable and easier to maintain than the second", it's just a matter of using proper layout and the second technique becomes very clear: and of course the chiastic order is what enables sensible layout to indicate clearly that what we have here is a nest, and like nested constructs in most notations the order of expression evaluation is from inside to outside (exactly as it is when the nesting levels are shown by brackets). His pseudocode in the text is utterly misleading - it shows joins that don't exist in the real code - and if that's how he thinks of the "second technique" (and his explanation following the pseudocode refers to a join between T2 and T3, which is one of the non-existent ones, so it probably is how he thinks of it) it's not at all surprising that he finds it hard to understand.

    If that pseudocode is changed from

    T1 <join_type> T2

    T2 <join_type> T3

    T3 <join_type> T4

    ON T4.key = T3.key

    ON T3.key = T2.key

    ON T2.key = T1.key

    to

    T1 <join_type>

    T2 <join_type>

    T3 <join_type> T4

    ON T4.key = T3.key

    ON T3.key = T2.key

    ON T2.key = T1.key

    it becomes clear what's going on, the imaginary joins are removed so that only the joins that actually ocur are shown and the layout shows what is joined with what.

    Tom

  • Gazareth

    One Orange Chip

    Points: 27737

    Toreador (7/8/2014)


    Being pedantic, only 3 of those statements return syntax errors...

    Are you running it on SQL 2008 R2 or earlier?

    Statement 6 is valid there but not on 2012 onwards.

  • Toreador

    SSChampion

    Points: 11256

    Gazareth (7/8/2014)


    Toreador (7/8/2014)


    Being pedantic, only 3 of those statements return syntax errors...

    Are you running it on SQL 2008 R2 or earlier?

    Statement 6 is valid there but not on 2012 onwards.

    SQL2014.

    Statement 3 fails with "An object or column name is missing or empty...."

    The others fail with a syntax error.

    I did say I was being pedantic 😉

  • pmadhavapeddi22

    SSCertifiable

    Points: 5306

    I have searched all the deprecated / discontinued features in 2012 but could not find "compute", and got it wrong 🙁 :crying:

    ( I had to run the code in 2008 R2 as I don't have 2012)

  • SQLRNNR

    SSC Guru

    Points: 281243

    nice question.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • Hany Helmy

    SSChampion

    Points: 13488

    pmadhavapeddi22 (7/8/2014)


    I have searched all the deprecated / discontinued features in 2012 but could not find "compute", and got it wrong 🙁 :crying:

    ( I had to run the code in 2008 R2 as I don't have 2012)

    Same here 🙂

Viewing 15 posts - 1 through 15 (of 20 total)

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