A Difference Between ISNULL And COALESCE You Might Care About In SQL Server

A Difference Between ISNULL And COALESCE You Might Care About In SQL Server

---

Thanks for watching!

Video Summary

In this video, I delve into the nuanced differences between `ISNULL` and `COALESCE` in SQL Server queries, specifically focusing on their performance implications and practical usage. Erik Darling from Darling Data provides a detailed analysis of these functions, highlighting that while both can be used interchangeably for most cases, `ISNULL` offers some unique advantages due to how it interacts with non-nullable columns. I also discuss the potential pitfalls of using these functions in join or where clauses, emphasizing their unnecessary use and the resulting suboptimal query plans. Additionally, I share my personal insights on consulting services and training opportunities, offering a discount code for those interested in cost-effective SQL Server education.

Full Transcript

Erik Darling here. That was very unclear. Erik Darling here with Darling Data. Look at all the Darling Data we have. It’s amazing. You’ve never seen so much Darling Data in your life. In today’s video, we’re going to talk about a substantive, substantial difference between isNull and coalesce in your SQL Server queries. Before we get into that, we’re going to talk about a substantial difference between isNull and coalesce in your SQL Server queries. So, just a few things up front about that though. Just a few things up front about my life. If you like this channel, there are low-cost ways to say, thanks for recording videos and publishing videos for free constantly. Which I guess kind of makes them not for free, but otherwise you would have to do other things to say thank you. Like, like, or comment or subscribe. So, you know, there’s that. If you are in need of SQL Server consulting, if you are having health performance emergencies, if you need someone to fix your crap for you, or if you need someone to train your developers so they stop producing crap. I’m pretty good at all of those things. If you need something else, let me know what it is. My rates are reasonable.

If you need some training that doesn’t cost a billion dollars a year, you can get all of mine for life for 75% off with that discount code. And of course, there’s a link with the discount code baked right into it in the description of the video. If you click on that, or maybe copy and paste it, I’m not quite sure what the mechanic is there. You can get everything for about 150 US dollars. So, that’s a pretty good deal. As far as, like, where I’ll be going in my life, well, Friday, September 6th, I have a full day pre-con for Data Saturday Dialist. You can show up there, you can learn about SQL Server, and then you can leave and forget about SQL Server until you need it. But that’s the beauty of it.

And then, November 4th and 5th, I will be at Past Data Summit in Seattle, co-presenting two wonderful days of SQL Server performance pre-cons with Kendra Little. We are going to kick butt. And now, let’s get on with the show here, apparently. That’s what our job is, getting on with shows. All that good stuff. So, when it comes to IsNull and Coalesce, there are, like, functional differences, where, like, Coalesce takes multiple inputs. Great.

But, you know, whenever you read on the internet, like, what’s faster, IsNull or Coalesce? People will do the same stupid performance tests, where, like, they’re just in a select list. And you’re really not going to find much of anything when you just stick IsNull and Coalesce in a select list.

It’s trivial. The real difference for me between IsNull and Coalesce is that IsNull has some superpowers that Coalesce does not. See, Coalesce has an ANSI standard function, and under the covers, it’s a case expression.

And if you dig a little bit deeper, a case expression is just an if statement way down deep. But that’s what it is. It’s not special.

Well, Microsoft is want to do. Didn’t really, like, improve upon Coalesce or even attempt to. They just made it write a case statement out behind the scenes, and that’s all you’ve got. So, one thing that IsNull can do that I think is a superpower is when you have a column that is actually not nullable, you do not allow nulls in that column, SQL Server can skip the IsNull.

And say, well, whatever, it’s not null anyway. It can’t do that with Coalesce. Coalesce still builds out the case expression in either case.

So, I did the needful, and I ran these two queries before I started recording, because you can’t see it, but if we go to the armpit zone, that’s probably a bad name for it. I should call it the rib meat zone, maybe. The Cote de Boeuf.

If, then, you’ll see that there’s about a minute and nine seconds of execution time under there, and, I mean, well, I am fully capable of blathering on for a minute and nine seconds. I didn’t feel like it. I just wanted to cut to the chase a little bit.

So, let’s look at these two query plans. Ah! Stop doing that. You murdered me. Now, we have an index on the votes table on creation date comma vote type ID. So, creation date is the leading column in the index.

This is, of course, the bigger deal for Sorgability stuff, like when the leading column of the index is the thing that you put the function on. Residual predicates, it’s like, well, no, whatever anyway. Like, it’s going to be probably a residual predicate almost no matter what you do.

So, whatever. I mean, SQL Server can do multi-seeks, but if you look in my video history, you’ll find some videos about multi-seek query plans where things don’t go well. They evaluate a lot of data depending on how they’re written.

So, looking at these two things, rather, let’s go back to these query plans here. For the first query, which is pretty fast, right? It’s about two seconds.

We seek into that index, right? Even though we have that creation date column wrapped in is null, since creation date is not a nullable column, SQL Server throws it out and we still seek right into the index, which is a pretty good plan, right? Look at that.

There is no mention of is null in this predicate whatsoever. Nothing. Nothing at all. It’s wonderful, right? It’s great. There’s no is null.

It’s just a scalar operator. Wonderful. Good for us, right? We figured it out. We cracked the case. If we go look at the index scan down here, you will see a case expression. All right?

You see that? All this case expression in here. Oh, you know what? Of course, I covered up the… There’s the case and there’s the end. And… Coalesce doesn’t shortcut or short circuit the way is null does.

And that leads us to get a really terrible query plan. In this case, the terrible query plan that we’re concerned about is a top above a scan. I can’t begin to tell you how many times I’ve seen this particular pattern in a query plan.

And the query is awful. A top above a scan is almost never a good sign. And if you run the query and get the actual execution plan, you can almost guarantee that this will be the absolute slowest part of the query.

So, what did we learn today? Coalesce? Just a case expression.

No superpowers. Is null. If you, you know, for some reason… And, you know, you see this with developers a bit where they don’t actually know their data. They don’t love their data.

They don’t spend any quality time getting to fall in love with their data. They just, you know, do random things. Write random queries. Copy stuff from other places and paste it in. Ask chat GPT to write a query for them.

And a lot of the times the result is going to be the same. There’s going to be just unnecessary is nulling of things. I see it quite a bit.

And it’s depressing every single time. So, if you’re trying to choose between which function to use, assuming that there is no functional requirement for you to use Coalesce, I generally do prefer to use is null because SQL Server can do some stuff with it that it can’t do with Coalesce.

If we’re talking about preferences generally, you shouldn’t have is null or Coalesce in a join or where clause because you’re asking for trouble. But, at least with is null, Microsoft can at least bail you out of a little bit of that trouble when the query runs because it will look at the column and say, hey, that column can’t be null anyway.

We don’t need to mess around here. So, there we go. I hope you enjoyed yourselves.

I hope you learned something. I hope that you will continue to not put is null and Coalesce in your join and where clauses. And, well, I suppose that’s probably the bigger lecture point is don’t use either one.

But, if you’re going to be that dumb, be a little bit less dumb and probably just use is null. Yeah. So, there we go.

We got a little bit less dumb today. That’s the goal, right? A little bit less dumb every day. Less dumber by the day. That’s us. All right.

Cool. I’m going to record some other stuff now. You might see some file names up at the top that might indicate what we’re going to be getting after over some of the next few videos.

And, well, it’s going to be a grand old time. So, anyway, once again, thank you for watching. Thank you.

Going Further

---

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.