No Answers
I wish I had an answer here, because sometimes when I think about these things it feels a bit silly.
Maybe someday.
This is the post I wrote over at Brent’s site that I reference in the video.
Video Summary
In this video, I delve into the intriguing behavior of SQL Server indexes when dealing with null values, specifically focusing on why a perfectly good index might be scanned rather than sought using `IS NULL` conditions. I walk through various examples, from scenarios where no indexes are present to cases involving an indexed column that is always null, exploring how these situations impact query plans and the optimizer’s decision-making process. Through these demonstrations, I aim to spark discussion on why SQL Server struggles with determining if a column filled entirely with nulls can be efficiently sought using `IS NULL`, even when the index data is known to contain only null values.
Full Transcript
Erik Darling here with Erik Darling Data. That’s cool, isn’t it? I’m hoping that second time’s a charm. The first time I recorded this video, I had the screen recorder positioned in the wrong place, so I missed like three-ish quarters of what I meant to record. So hopefully this time, everything is coming up lucky for me. And I wanted to ask this question, because I think it’s a question that will lead to a lot of other questions getting answered. And I think it’s an interesting question, specifically for databases, because of the way that indexes store data. And that question is, why, if we know the order of a column, does using something like isNull on that column result in a perfectly good index being scanned? I’m going to give you some examples. So let’s start off with nothing, right? We have no indexes on this table. I have query plans turned on. And if I run this query, I get two results back, and they are not going to be scanned.
So they are both the same result. 2465713. That is as phony as 50% of the phone numbers that I have gotten in my life. And when I look at the execution plan for these two phony phone numbers, I will see that they did roughly the exact same thing. The top query scan the top query scan the entire clustered index, read all 2465713 rows, and did not ask for an index because I wouldn’t expect it to because there’s no where clause. What would you index to improve this? There’s nothing. But the second query, where I say select count from the user’s table where age is null, I get a missing index request because there is a where clause where a SQL Server could filter data where it is null.
Not very interesting there at all. Where things start to get interesting to me is when I run queries like this. So let’s say that knowing that the entire age column is null, and knowing that nothing has changed in the database, there are no modifications to that column, like what would have gotten changed in the last two seconds?
I’m but a lowly consultant with just a simple demo database working for Erik Darling data. There we go. There we go. There’s that slow-mo bounce that I was looking for. If I run these two queries, I mean, I’m not going to get a missing index request.
Like SQL services like, index on age? Nah. Could never help. What would we do with that? How would we know? How would we know if it was null? We would never know. It’s crazy. It’s crazy to think about.
Even though in the first query we were just replacing a column that’s all null with a null, and in the second query we were replacing a column that’s all null with an empty string, you should be able to figure this out. SQL Server, you cost $7,000 a core.
For a standard edition, we can’t even get you to take more than 128 gigs of memory for the buffer pool. But you can’t figure out if a column that’s all null is null. This is what we’re stuck with.
I mean, if you budged on the memory thing, we might forgive you for this, but… Mamacito, what’s going on? This all gets even more interesting when we actually add an index that could be helpful.
So let’s add an index on this age column, which is always null. Now, it wasn’t always null, but I have it on the authority of the most senior DBA at Stack Overflow. That they recent…
Though, not recently, actually. They broke all my demos like two years ago. They started nullifying this column because of GDPR. So if you want to re-break my demos, I don’t know, repeal GDPR, do something like that.
So this is where things start to get a little bit more interesting for me, is now that we have an index on age just by itself, if we run a count against the age column where age is null, and we look at the query results and the query plan, we now have an index seek.
And this is an important question for all you people out there who get very hung up on if indexes have seeked or scanned to find their data. Is it really a seek if we read the entire index?
Hmm. Hmm. No. I hate to spoil this.
No, it’s not. Likewise, if we run this query where we say select count from users where is null age replaced with a null is null, all of a sudden, I mean, we no longer seek.
We now scan to read the entire index, but… Who cares? I think what’s interesting here is that even though we know that that entire index comprises null values, we do not…
We are not able to seek to any portion of it that might be null. Now, if this were a column that had some null values and some not null values, we create the index on it.
The index data would be sorted by nulls, and then whatever comes sequentially next. If it’s a number column, it could be negative numbers, and then positive numbers.
If it’s a string column, it could be numbers and then letters. It doesn’t matter too much. We just know that nulls come first. There should be no real pathological forensic reason for nulls to come last.
Even if we had the index definition as sort of descending, like what’s the worst that happens? We replace nulls at the end rather than nulls at the beginning.
Now, we have covered examples over several iterations of these queries where we have replaced the null in the column, in the indexed column, with a literal value, either a null or another number, or I think that’s all I’ve done.
But in all of these cases, we have had to scan. Now, I’m less forgiving for these cases because these cases are manure to me. The optimizer is very smart.
It’s been worked on by doctors for 30 years. If I had doctors working on me for 30 years and I still looked like this, I would get my money back. The optimizer has had doctors working on it for 30 years, and it still can’t figure out where nulls fall in an index.
That’s a little weird to me. Now, where I am forgiving, now where I understand why this might cause some weirdness, is if, let’s say, we said is null, a column, and then another column.
I’m partially forgiving of this. And I say I’m only partially forgiving of this because under normal circumstances, if you run is null against a column that does not allow nulls, it will skip the is null check, which is a departure from the coalesce function, which is internally a case expression, which will check that column regardless.
I blogged about this a while back on Brent’s site. If you’re really lucky, I’ll put a link to it in the description of this video. But that is absolutely true. If you have a not nullable column and you say is null not nullable column equals something, SQL Server will say I don’t need to run an is null check on this because I know that this column is not nullable.
Coalesce, it will still run the entire internal case expression. So if you’re ever looking for a reason to use is null over coalesce, there you have it. So where I am more forgiving is, let’s take an example where we select a count from users where we say is null age, even though we know all of age is null.
And then we compare it, maybe replace those null values for the count ID, which is at least a nullable column. Well, okay, fine.
If they’re not in an index together and they’re not sorted together, I totally understand why this would be confusing. But in the case where we take a column like age and replace it with the primary key clustered index, which you know is not nullable, but this could go for any column that is not nullable.
It doesn’t have to be this. I’m a little bit less understanding. Why would you be so confused over what’s null or not? So I think that a pretty fair thing to ask for would be if we have is null wrapped around a column that is indexed and we have that is null expression replacing the nullable column with a literal value, that literal value should be applied to the constant folding portion of index optimization.
We should take that literal value and we should be able to compare that pretty easily to another literal value. I don’t expect this to go for where is null age, some other value equals another column, or is null age, some other value equals, what was I saying?
No, is null age, another column equals something, but I do expect that like if we have an index on age, we should be able to at least see to that, like bare minimum, bare minimum, because we know, we know what’s going on in there.
We have that data sorted the way we want it. Anyway, I think I can hear my wife yelling at me through the door, so I’m going to get going and I’m going to go watch a television program.
I’m going to finish my tea while I watch a television program. But thank you for watching. I hope that I get to see you live and in person at an event so that I can give you a cool sticker.
Look at the rainbows on that thing. You are not having a stroke. You are seeing cool rainbows. So anyway, I hope to see you so I can give you a sticker.
I hope that you enjoyed this video. I hope that you at least thought about something, especially if you work at Microsoft. I hope you thought about something. And I will see you in another video, another time, another place.
Farewell.
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 25% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.