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]

Nick Doyle SSC Eights! Points: 926 More actions · Answer 1

Nick Doyle

SSC Eights!

Points: 926

August 8, 2013 at 9:47 am

#1639864

Very good QoTD, thanks!

SQLRNNR SSC Guru Points: 281334 More actions · Answer 2

Very good question. I learned something new.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Ken SSCoach Points: 17191 More actions · Answer 3

Very nice question. Challenging, but still fun. Thanks!

sneumersky SSCertifiable Points: 7667 More actions · Answer 4

....and I thought my wife asked the toughest questions. My brain came to a screeching halt on this one :w00t:

sestell1 SSChampion Points: 10230 More actions · Answer 5

crussell-931424 (8/8/2013)
Interesting. I didn't realize that nullif is really a case statement and that any function would then get executed multiple times. Of course in this case the function is NewID which of course returns different results every time it is run.

Yea, the IN clause is also interpreted as a series of CASE statements:

PRINT CASE WHEN CONVERT(INT, RAND() * 2 + 1) IN (1, 2) THEN 'OK' ELSE 'INVALID' END

GO 20

palotaiarpad SSCertifiable Points: 5617 More actions · Answer 6

palotaiarpad

SSCertifiable

Points: 5617

August 8, 2013 at 12:17 pm

#1639934

A checksum can be negative. It's new for me.

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

sestell1 (8/8/2013)
crussell-931424 (8/8/2013)
Interesting. I didn't realize that nullif is really a case statement and that any function would then get executed multiple times. Of course in this case the function is NewID which of course returns different results every time it is run.
Yea, the IN clause is also interpreted as a series of CASE statements:
PRINT CASE WHEN CONVERT(INT, RAND() * 2 + 1) IN (1, 2) THEN 'OK' ELSE 'INVALID' END
GO 20

It's actually interpreted as a series of conditioned, OR'ed together. The above is equivalent to

PRINT CASE WHEN CONVERT(INT, RAND() * 2 + 1) = 1

OR CONVERT(INT, RAND() * 2 + 1) = 2

THEN 'OK'

ELSE 'INVALID' END;

GO 20

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/

PHYData DBA SSCertifiable Points: 7541 More actions · Answer 8

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

Question was which of the following could be returned.

Zero is possible but was not in the list being questioned.

Think about it. Question is correct.

PHYData DBA SSCertifiable Points: 7541 More actions · Answer 9

Nice question. Even Better your explanations.

If only I had paid enough attention to see it was multiple options....

aochss SSCommitted Points: 1677 More actions · Answer 10

Great question (I like that you included explanations of the build in function).

However, I cannot see the use/value of the "NULLIF" construct. In what situations would you use something like this?

Has anyone used this in a production environment?

Anton

mister.magoo SSC-Forever Points: 47174 More actions · Answer 11

aochss (8/9/2013)
Great question (I like that you included explanations of the build in function).
However, I cannot see the use/value of the "NULLIF" construct. In what situations would you use something like this?
Has anyone used this in a production environment?
Anton

Thanks for the kind remarks.

NULLIF can be very useful, consider this one example:

SELECT margin_percent = 100 * FLOOR(margin / cost)

What happens when cost is zero? it blows up!

Now this

SELECT margin_percent = 100 * FLOOR(margin / ISNULL(NULLIF(cost,0),margin))

Now, if the cost is zero it is replaced by the margin value and the calculation returns 100%, which is what you would want.

MM

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

Jason Wolfkill SSCrazy Eights Points: 9832 More actions · Answer 12

I figured the answer had something to do with the effect of using non-determinative functions and the NULLIF(), because if I stripped away the ISNULL() and ran this:

SELECT NULLIF(CONVERT(varchar, SIGN(CHECKSUM(NEWID()))), '-1')

I could see the NULLIF() returning '1', '-1', and NULL in defiance of an expectation that it would never return '-1'.

mister.magoo, did you verify that SQL Server rewrites NULLIF() as a CASE expression by direct observation, and if so, how (I couldn't find an execution plan for the query)? Or is this documented somewhere (if so, I must admit that my Google-fu is weak today)?

Jason Wolfkill

Thomas Abraham SSChampion Points: 10761 More actions · Answer 13

Thanks for the question. I see you've let the adulation already go to your head. 🙂

The follow-on discussion was great too. Thanks, as always for Hugo's contributions.

[font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
Connect to me on LinkedIn

paul.knibbs SSCoach Points: 15320 More actions · Answer 14

wolfkillj (8/9/2013)
Or is this documented somewhere (if so, I must admit that my Google-fu is weak today)?

It's definitely documented (which I wish I'd read before answering the question incorrectly):

http://technet.microsoft.com/en-us/library/ms177562.aspx

See the section under "Remarks" near the top.

Jason Wolfkill SSCrazy Eights Points: 9832 More actions · Answer 15

paul.knibbs (8/9/2013)
wolfkillj (8/9/2013)
Or is this documented somewhere (if so, I must admit that my Google-fu is weak today)?
It's definitely documented (which I wish I'd read before answering the question incorrectly):
http://technet.microsoft.com/en-us/library/ms177562.aspx
See the section under "Remarks" near the top.

Hi Paul,

I did see that remark that "NULLIF is equivalent to a searched CASE expression", but I was curious about whether magoo actually observed that the NULLIF() was converted to the CASE expression in the explanation (i.e., he typed a NULLIF() in his query and saw it represented as the CASE syntax in the execution plan, much like a BETWEEN is represented as a ">=/<=" pair in a predicate) or has an authoritative source that documents the behavior.

I finally figured out a way to see an execution plan demonstrating how SQL Server treats the NULLIF() using a SELECT . . . FROM a table with several million rows and an index on an integer key with a condition referencing my tally table, like this:

SELECT integerKey,

NULLIF(CONVERT(VARCHAR, SIGN(CHECKSUM(NEWID()))),'-1') AS col1

FROM dbo.millionsOfRows

WHERE integerKey IN (SELECT n FROM dbo.tally WHERE n BETWEEN 1 AND 1000000)

This query is complex enough to generate an execution plan, which includes a Compute Scalar with this Defined Value:

[Expr1005] = Scalar Operator(

CASE WHEN CONVERT(varchar(30),sign(checksum(newid())),0)='-1' THEN NULL

ELSE CONVERT(varchar(30),sign(checksum(newid())),0)

END)

So that answers my question - it can be observed directly that SQL Server rewrites NULLIF() into a CASE expression.

Regards,

Jason

EDIT: fixed the code formatting

Jason Wolfkill