T-SQL Syntax Gotchas

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

    The SQL Guy @ blogspot[/url]

    @SeanPearceSQL

    About Me[/url]

  • Nice One.

    I did quick search for COMPUTE. 😉

    Thanks

  • Thank you for the question.

  • Great question, thanks!

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

  • Nice question, Thanks for sharing

  • 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

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

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

  • 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

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

  • 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 😉

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

  • 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

  • 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