BETWEEN a hard place and a rock

  • Comments posted to this topic are about the item BETWEEN a hard place and a rock


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • Interesting question.....completely missed and gone off-track, feeling like "pressed between the hard place and rock" 🙂

    ~ Lokesh Vij


    Guidelines for quicker answers on T-SQL question[/url]
    Guidelines for answers on Performance questions

    Link to my Blog Post --> www.SQLPathy.com[/url]

    Follow me @Twitter

  • Lokesh Vij (6/11/2013)


    Interesting question.....completely missed and gone off-track, feeling like "pressed between the hard place and rock" 🙂

    +1 🙂

    Thanks
    Vinay Kumar
    -----------------------------------------------------------------
    Keep Learning - Keep Growing !!!

  • very "nasty" question missed it all together - lost in translation - between computer screen and my brain :p

    Hope this helps...

    Ford Fairlane
    Rock and Roll Detective

  • Wonderful!

    Regards,

    IgorMi

    Igor Micev,
    My blog: www.igormicev.com

  • Thanks for the nice question with plenty of knowledge involved, but I've got it just because none of the above made sense for me, and the explanation is not 100% correct as even if we have 5 Null values in the table, the operator EXCEPT will act as explained in BOL:

    EXCEPT returns any distinct values from the left query that are not also found on the right query.

    So it will never ever return 5 rows, if NULL values come to play, there will be just one row with NULL value in it.

    declare @t table (numericColumn int)

    insert into @t values (-10)

    insert into @t values (-9)

    insert into @t values (-8)

    insert into @t values (-7)

    insert into @t values (-6)

    insert into @t values (-5)

    insert into @t values (-4)

    insert into @t values (-3)

    insert into @t values (-2)

    insert into @t values (-1)

    insert into @t values (0)

    insert into @t values (1)

    insert into @t values (2)

    insert into @t values (3)

    insert into @t values (4)

    insert into @t values (5)

    insert into @t values (6)

    insert into @t values (null)

    insert into @t values (null)

    insert into @t values (null)

    insert into @t values (null)

    insert into @t values (null)

    SELECT *

    FROM @t

    EXCEPT

    SELECT *

    FROM @t

    WHERE NOT (NumericColumn BETWEEN -1 AND -10);

    Cheers

  • raulggonzalez (6/12/2013)


    Thanks for the nice question with plenty of knowledge involved, but I've got it just because none of the above made sense for me, and the explanation is not 100% correct as even if we have 5 Null values in the table, the operator EXCEPT will act as explained in BOL:

    EXCEPT returns any distinct values from the left query that are not also found on the right query.

    So it will never ever return 5 rows, if NULL values come to play, there will be just one row with NULL value in it.

    Good point, and maybe I should have included that in my explanation.

    What you forget, though, is that EXCEPT applies the distinct algorithm to the entire result of the SELECT. And I think it's safe to assume that the table has more columns than just the NumericColumn used in the BETWEEN. In your repro, if you add a second column and make sure it has different values in the five rows with a NULL in NumericValue, you'll get 5 rows returned.


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • Ford Fairlane (6/12/2013)


    very "nasty" question missed it all together - lost in translation - between computer screen and my brain :p

    Thanks! (Goes for the other replies as welll).

    I was aware that this is a nasty question. I deliberately picked a title that at least gives a hint about the most nastiest incorrect answer.


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • More proof that one should not attempt QOTD before the morning dose of caffeine.

  • batgirl (6/12/2013)


    More proof that one should not attempt QOTD before the morning dose of caffeine.

    Great one 😀

    Thanks
    Vinay Kumar
    -----------------------------------------------------------------
    Keep Learning - Keep Growing !!!

  • batgirl (6/12/2013)


    More proof that one should not attempt QOTD before the morning dose of caffeine.

    +1 Great question Hugo. I completely overlooked the hint in the title. I also seemed to have overlooked half the question too! :hehe:



    Everything is awesome!

  • This was removed by the editor as SPAM

  • Nice Question Hugo..

    Thanks..

  • Excellent question Hugo. I think the most disturbing thing is that at the time of this posting 38% of responders think there are rows that between -1 and -10!!! 😛

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Nice question Hugo!

    Unfortunately I fell for the trap in the title.

    Evidentally I have more studying to do of the RCS isolation level. :ermm:

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

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