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

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