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