Post reply

Tally Tables and Table Variables

  • November 9, 2017 at 9:01 pm

    #77491

    Comments posted to this topic are about the item Tally Tables and Table Variables

  • November 10, 2017 at 1:34 am

    #1967492

    Weird, but instructive - thank you!

  • November 10, 2017 at 3:25 am

    #1967497

    Really enjoyed that one. Thanks!

  • November 10, 2017 at 6:10 am

    #1967511

    The underlying problem, of course, is that CHAR() is both a data type and a function. 
    Add to that the fact that AS can be used in declaring the type of a variable or parameter (DECLARE @a AS INT) but NOT in the type of a column (because it's reserved for identifying a computed column), and you've got SQL that looks like it does one thing at first glance, but in fact does something completely different.

  • November 10, 2017 at 6:48 am

    #1967515

    sknox - Friday, November 10, 2017 6:10 AM

    The underlying problem, of course, is that CHAR() is both a data type and a function. 
    Add to that the fact that AS can be used in declaring the type of a variable or parameter (DECLARE @a AS INT) but NOT in the type of a column (because it's reserved for identifying a computed column), and you've got SQL that looks like it does one thing at first glance, but in fact does something completely different.

    Very well stated, Sknox.

  • November 10, 2017 at 10:36 am

    #1967555

    Amusing problem, but the correct answer would be "the insert attempts to specify values for a computed coulmn" which isn't any of the options available.  
    There is no problem whatever in inserting into a table that has computed columns unless the insert tries to  specify values for one (or more) of them. 

    So the "correct answer" is not actually the correct answer. 

    Of course since none of the other answer optons mention a computed column, it's obvious which incorrect answer was intended to be the correct answer, so the incorrect correct answer issue is amusing rather than serious and shouldn't have led anyone astray.  🙂

    Tom

  • November 10, 2017 at 12:10 pm

    #1967566

    What truly amazes me is the number of people that selected either of the first 2 answers.  I can understand how someone might make the mistake of the last one if they don't know what a Tally table is, didn't understand the MyTally cte, and missed the AS.

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

  • November 10, 2017 at 12:11 pm

    #1967567

    p.s. Cool problem, BTW.

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

  • November 10, 2017 at 4:33 pm

    #1967612

    Jeff Moden - Friday, November 10, 2017 12:10 PM

    What truly amazes me is the number of people that selected either of the first 2 answers.  I can understand how someone might make the mistake of the last one if they don't know what a Tally table is, didn't understand the MyTally cte, and missed the AS.

    I guess the heading "Tally tables and table variables" will have made some people pick the second answer (especially as for a lot of people the only thing they "know" about table variables is that "they are a bad thing" so they'y haven't bothered to learn anything about them)  but I can't imagine any way people would fall for the first one - what do they think a CTE is for heaven's sake?   And the only way anyone can fall for the last one is if they don't know that INSERT automatically does any neccessary padding for fixed length strings so there's no need for replicate to do it.

    Tom

  • November 10, 2017 at 5:29 pm

    #1967615

    Totally missed seeing the AS.

  • November 11, 2017 at 4:28 am

    #1967632

    Marcia J - Friday, November 10, 2017 5:29 PM

    Totally missed seeing the AS.

    I saw it, but I was doing some VBA yesterday, so my brain automatically interpreted it as a data type declaration.  D'oh!

  • November 12, 2017 at 8:21 am

    #1967685

    Lesson for those who got this one wrong, AS in the column definition within the DDL means that it is a computed column.
    😎

  • November 12, 2017 at 10:27 am

    #1967689

    Eirikur Eiriksson - Sunday, November 12, 2017 8:21 AM

    Lesson for those who got this one wrong, AS in the column definition within the DDL means that it is a computed column.
    😎

    Lesson even for those that almost got it wrong because of poor eye sight... enlarge the code and read it carefully with your glasses on. 😉

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

  • November 13, 2017 at 4:18 am

    #1967736

    Nearly missed the AS, myself
    fortunately, have just had my second cup of coffee, saw it and the only logical answer  became apparent.
    Nice riddle, thanks Steve

    ____________________________________________
    Space, the final frontier? not any more...
    All limits henceforth are self-imposed.
    “libera tute vulgaris ex”

  • November 14, 2017 at 6:45 am

    #1967948

    Stewart "Arturius" Campbell - Monday, November 13, 2017 4:18 AM

    Nearly missed the AS, myself
    fortunately, have just had my second cup of coffee, saw it and the only logical answer  became apparent.
    Nice riddle, thanks Steve

    I'm in the same boat at you, Stewart.  I saw it, but it didn't register until reading the answers.  Nice question.

    Tally Tables - Performance Personified
    String Splitting with True Performance
    Best practices on how to ask questions

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

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