Variables, Nulls and Coalesce

  • Kevin Gill

    SSCrazy

    Points: 2316

    Comments posted to this topic are about the item Variables, Nulls and Coalesce

    -------------------------------
    Oh no!

  • cengland0

    SSCertifiable

    Points: 6102

    I almost selected the option that said there would be an error on the first select because the query should return more than one value.

    select @a = iValue from @vals where iKey = iValue order by iKey

    After thinking about it and before submitting my answer, I realized it would work but pick the last item and assign it to @a.

    This one almost got me.

  • honza.mf

    SSCertifiable

    Points: 5519

    :w00t:

    I know very well how this assignments work. So it was just my great answering problem: Don't miss the right radio button. 😀



    See, understand, learn, try, use efficient
    © Dr.Plch

  • Carlo Romagnano

    SSC-Insane

    Points: 21943

    WARN: If you use an aggregate function @a will be updated also if no row is returned

    [font="Arial"]SET NOCOUNT ON

    declare @vals table (iKey int, iValue int)

    insert into @vals (iKey, iValue)

    select 1,1

    union select 2,2

    union select 3,3

    declare @a int

    set @a = 5

    select @a = iValue from @vals where iKey = iValue order by iKey

    select @a

    select @a = iValue from @vals where iKey <> iValue

    select @a

    select @a = COUNT(*) from @vals where iKey <> iValue

    select @a[/font]

  • cengland0

    SSCertifiable

    Points: 6102

    Carlo Romagnano (11/25/2009)


    WARN: If you use an aggregate function @a will be updated also if no row is returned

    [font="Arial"]SET NOCOUNT ON

    declare @vals table (iKey int, iValue int)

    insert into @vals (iKey, iValue)

    select 1,1

    union select 2,2

    union select 3,3

    declare @a int

    set @a = 5

    select @a = iValue from @vals where iKey = iValue order by iKey

    select @a

    select @a = iValue from @vals where iKey <> iValue

    select @a

    select @a = COUNT(*) from @vals where iKey <> iValue

    select @a[/font]

    Wouldn't you always get a row returned with the COUNT(*) aggregate? It might return 0 as the result but that still returns a row.

  • Carlo Romagnano

    SSC-Insane

    Points: 21943

    Wouldn't you always get a row returned with the COUNT(*) aggregate? It might return 0 as the result but that still returns a row.

    That's true!

  • Kevin Gill

    SSCrazy

    Points: 2316

    cengland0 (11/25/2009)

    Wouldn't you always get a row returned with the COUNT(*) aggregate? It might return 0 as the result but that still returns a row.

    Indeed - if you slot an aggregate that returns NULL into the query around the 'iValue' in the final COALESCE, you actually get zero, as the MAX aggregate (to take an example at random) actually returns a row with NULL in.

    Interesting observation. 🙂

    -- Kev

    -------------------------------
    Oh no!

  • Tom Garth

    SSCertifiable

    Points: 6173

    The 2nd SELECT has no ORDER BY clause, and the table variable has no index. How do you know what the last row will contain?

    😛

    Tom Garth
    Vertical Solutions[/url]

    "There are three kinds of men. The one that learns by reading. The few who learn by observation. The rest of them have to pee on the electric fence for themselves." -- Will Rogers
  • gary.rumble

    SSCommitted

    Points: 1763

    Tom Garth (11/25/2009)


    The 2nd SELECT has no ORDER BY clause, and the table variable has no index. How do you know what the last row will contain?

    😛

    The second query returns null so the variable isn't modified from the first query.

  • honza.mf

    SSCertifiable

    Points: 5519

    gary.rumble (11/25/2009)


    Tom Garth (11/25/2009)


    The 2nd SELECT has no ORDER BY clause, and the table variable has no index. How do you know what the last row will contain?

    😛

    The second query returns null so the variable isn't modified from the first query.

    I hope it was a joke (see emoticon)

    :w00t:



    See, understand, learn, try, use efficient
    © Dr.Plch

  • Tom Garth

    SSCertifiable

    Points: 6173

    Ya got me.

    :crying:

    Tom Garth
    Vertical Solutions[/url]

    "There are three kinds of men. The one that learns by reading. The few who learn by observation. The rest of them have to pee on the electric fence for themselves." -- Will Rogers
  • dun

    Say Hey Kid

    Points: 708

    This was a great instructional puzzle. I appreciate the corporate knowledge this forum represents. Thanks.

  • Kevin Gill

    SSCrazy

    Points: 2316

    dun (11/26/2009)


    This was a great instructional puzzle. I appreciate the corporate knowledge this forum represents. Thanks.

    Glad you enjoyed it 🙂 This example actually came to mind because of the amount of times I fell over things like this when I was doing serious T-SQL programming for the first time a couple of years ago, when trying to improve and modify the thousands of lines of code left for us to ... try to deal with ... by an (SEI Level 5 - ha!) outsourcing company who wrote our then brand-new system.

    -- Kev

    -------------------------------
    Oh no!

  • Rob Goddard

    SSCrazy

    Points: 2572

    Nice question.

    Along the same lines of "no rows returned, do nothing", I have a feeling that it may be a case of "400 rows returned, assign 400 values to the same variable" so you are just left with the last one it assigned.

    My question is, if you're not doing anything with that variable (i.e. "select @val = col from tab" rather than "select @val = @val + col from tab"), does the optimizer spot this and skip all the variable assignments and just do the last one, or are you inadvertantly creating overhead by assigning every single value that you will never see?

    --------
    [font="Tahoma"]I love deadlines. I like the whooshing sound they make as they fly by. -Douglas Adams[/font]

  • Carlo Romagnano

    SSC-Insane

    Points: 21943

    Rob Goddard (11/27/2009)


    does the optimizer spot this and skip all the variable assignments and just do the last one?

    All the variable assignments are done.

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

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