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:
