Jerry Nixon @Work: Performance of SQL’s NullIf() Function (versus Case)

Jerry Nixon on Windows

Thursday, February 11, 2010

Performance of SQL’s NullIf() Function (versus Case)

Sometimes, courtesy functions surprise me. The core functions are pretty familiar to me. I think anyone do does significant SQL development can say the same thing.

In a previous post (here), I compared the performance between stripping time from DateTime using a variety of approaches. And (here) I compared IsNull() to Coalesce(). Sometimes built-in functions were better, sometimes not.

A colleague (that would be Wes) asked me about the tSQL function NullIf(). Naturally, since I had not heard of it, I assumed me meant IfNull(). But, as it turns out, the library of things I don’t know included (until yesterday) the tSQL function NullIf().

Let’s say you wanted to do this:


The syntax evaluates @x and @y. If they are equal it returns null, otherwise @x. That’s all it does.

Now, let’s see how to accomplish this with NullIf():


The results are exactly the same. The difference is syntax.

And if your maintenance developer does not know NullIf(), then he’ll need to pull up Books Online – and that’s okay.

But, who cares? You might ask. That’s what I thought. Then I wanted to test and see how they compared if I ran them both a few million times. I created this test scenario:



Get the code here:

Of course, I will be the first to admit that this little test violates every coding convention I have ever promoted! But, hey.

And the results are these:


And, look who’s 40% faster. The built-in function: NullIf()