BETWEEN a hard place and a rock

Question

Post reply

BETWEEN a hard place and a rock

Hugo Kornelis

SSC Guru

Points: 64790
More actions
June 11, 2013 at 10:49 pm

#276207

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/
June 12, 2013 at 7:23 am

This was removed by the editor as SPAM

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

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

Lokesh Vij SSChampion Points: 10836 More actions · Answer 1

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: 6099 More actions · Answer 2

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 More actions · Answer 3

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 More actions · Answer 4

Wonderful!

Regards,

IgorMi

Igor Micev,My blog: www.igormicev.com

SQLDoubleG Hall of Fame Points: 3100 More actions · Answer 5

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)

SELECT *

FROM @t

EXCEPT

SELECT *

FROM @t

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

Cheers

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 6

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: 64790 More actions · Answer 7

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 actions · Answer 8

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

Vinay Kumar SSCertifiable Points: 6099 More actions · Answer 9

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 More actions · Answer 10

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!

PChiragS SSCarpal Tunnel Points: 4965 More actions · Answer 11

PChiragS

SSCarpal Tunnel

Points: 4965

June 12, 2013 at 7:46 am

#1623346

Nice Question Hugo..

Thanks..

Sean Lange SSC Guru Points: 286590 More actions · Answer 12

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 More actions · Answer 13

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: