BETWEEN a hard place and a rock

  • Hugo Kornelis

    SSC Guru

    Points: 64685

    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/

  • Lokesh Vij

    SSChampion

    Points: 10836

    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

  • Vinay Kumar

    SSCertifiable

    Points: 6098

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

  • Ford Fairlane

    SSCertifiable

    Points: 7664

    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

  • Igor Micev

    SSC-Dedicated

    Points: 33109

    Wonderful!

    Regards,

    IgorMi

    Igor Micev,
    My blog: www.igormicev.com

  • SQLDoubleG

    Hall of Fame

    Points: 3090

    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

  • Hugo Kornelis

    SSC Guru

    Points: 64685

    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/

  • Hugo Kornelis

    SSC Guru

    Points: 64685

    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/

  • batgirl

    SSCarpal Tunnel

    Points: 4979

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

  • Vinay Kumar

    SSCertifiable

    Points: 6098

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

  • Dana Medley

    SSCertifiable

    Points: 6764

    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

  • PChiragS

    SSCarpal Tunnel

    Points: 4965

    Nice Question Hugo..

    Thanks..

  • Sean Lange

    SSC Guru

    Points: 286531

    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/

  • sestell1

    SSChampion

    Points: 10230

    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