Expressions

Question

Post reply

Expressions

Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

Kwex Default port Points: 1407 More actions · Answer 1

Only 18% of correct answers! Good question mate ...

ok181ko SSC Rookie Points: 35 More actions · Answer 2

paul.knibbs (8/8/2013)
ok181ko (8/8/2013)
How about
newid() = '00000000-0000-0000-0000-000000000000'
The question is not correct. Answer is 0 (zero).
The question asked which of the answers were possible outputs of the statement. It never stated that *all* of the possible outputs were listed as answers. Plus, is it even possible for NEWID() to return a value that's all zeroes?

And You can prove that this can not be?

srienstr SSCrazy Points: 2435 More actions · Answer 3

ok181ko (8/8/2013)
paul.knibbs (8/8/2013)
ok181ko (8/8/2013)
How about
newid() = '00000000-0000-0000-0000-000000000000'
The question is not correct. Answer is 0 (zero).
The question asked which of the answers were possible outputs of the statement. It never stated that *all* of the possible outputs were listed as answers. Plus, is it even possible for NEWID() to return a value that's all zeroes?
And You can prove that this can not be?

I can prove that particular value to be irrelevant:

select checksum('00000000-0000-0000-0000-000000000000')

-1448389036

Of course, this does not mean that there isn't a value for newid that would provide a checksum of 0, only that the value you selected is not it.

Puto me cogitare, ergo puto me esse.
I think that I think, therefore I think that I am.

Hugo Kornelis SSC Guru Points: 64780 More actions · Answer 4

srienstr (8/8/2013)
select checksum('00000000-0000-0000-0000-000000000000')
-1448389036

You're calculating the checksum of a value that just happens to loook remarkably like a uniqueidentifier value - but is just a plain old varchar.

select checksum(cast('00000000-0000-0000-0000-000000000000' as uniqueidentifier) )

Result: 0

(on my system)

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/

Tee Time Hall of Fame Points: 3745 More actions · Answer 5

Tee Time

Hall of Fame

Points: 3745

August 8, 2013 at 6:42 am

#1639727

Excellent question, thanks!

srienstr SSCrazy Points: 2435 More actions · Answer 6

Hugo Kornelis (8/8/2013)
srienstr (8/8/2013)
select checksum('00000000-0000-0000-0000-000000000000')
-1448389036
You're calculating the checksum of a value that just happens to loook remarkably like a uniqueidentifier value - but is just a plain old varchar.
select checksum(cast('00000000-0000-0000-0000-000000000000' as uniqueidentifier) )
Result: 0
(on my system)

Ah, I had not been aware of that distinction. I haven't even finished my first cup of coffee and I'm already learning, today should be a good day.

Puto me cogitare, ergo puto me esse.
I think that I think, therefore I think that I am.

alex.d.garland Right there with Babe Points: 749 More actions · Answer 7

As many others have said - great question, probably the best I've seen on the site. Challenging without being pedantic.

I did get it wrong but understand why and definitely feel that I've learnt something knew.

TomThomson SSC Guru Points: 104773 More actions · Answer 8

Good question. I managed to get it wrong by exercising my best carelessness. I really must stop answering questions without first engaging my brain and reading the question properly.

Tom

Daniel Bowlin SSC-Dedicated Points: 34566 More actions · Answer 9

Daniel Bowlin

SSC-Dedicated

Points: 34566

August 8, 2013 at 7:19 am

#1639753

Really good question, thanks.

Dana Medley SSCertifiable Points: 6764 More actions · Answer 10

Good question. I need to stop answering questions before I get woke up.

Everything is awesome!

Ed Wagner SSC Guru Points: 287026 More actions · Answer 11

Yes, that was a good one - it got me. :hehe:

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

mister.magoo SSC-Forever Points: 47068 More actions · Answer 12

james.of.rivendell (8/7/2013)
Moral of today's question. Be careful when using indeterministic functions inside nullif...

That's not the only place this can happen of course...

ok181ko (8/8/2013)
How about
newid() = '00000000-0000-0000-0000-000000000000'
The question is not correct. Answer is 0 (zero).

SQL Server will not produce that value from the NEWID() function as it outputs type 4 uuids, where the first digit of the third section is always "4"

634FAC37-2B2A-[highlight="#ffff11"]4[/highlight]5DF-8540-032ACE5C3A0B

However, I do concede that the checksum could possibly produce a zero for one or more guid values, at least I have no evidence to say it will not, so thanks for pointing out that zero may be a valid value as well.

The intention was to educate about the behaviour rather than about the possible values, so I think it still works on that level 🙂

Everyone Else
Nice reviews

Thanks for the nice feedback, I did try hard to make this question useful and informative, so it is pleasing to hear that so many of you enjoyed it. 🙂

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);