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:

image

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():

image

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:

image

image

Get the code here: http://www.codepaste.net/o5zpow

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:

image

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