How Indexes Talk To Each Other In SQL Server
Video Summary
In this video, I delve into the intricate world of indexes in SQL Server, specifically focusing on how they communicate and collaborate with each other. Starting off by addressing some feedback from recent recordings—such as the audio quality improvements and the mysterious “modulated” sound—I explain that while these issues are noted, viewers can now enjoy both high-quality video and audio versions of my content. The main topic centers around how indexes on the same table interact, particularly between clustered and nonclustered indexes. I demonstrate this through creating two indexes on a users table—one unique and one non-unique—and running queries to illustrate their behavior in query plans. By adding more columns to these indexes, I further clarify the dependencies and seek patterns that arise when querying such structures. The video concludes with an exploration of how SQL Server uses multiple nonclustered indexes together, showcasing techniques like index intersection and union, and contrasting them with key lookups.
Full Transcript
Erik Darling here with Darling Data. Unfortunately, Birgut Magazine has purchased the rights to the name Erik with a K, so I’m not sure how much longer I have using that. I’ll keep you updated on what it ends up getting changed to, is legal proceedings proceed. Anyway, I’m here on this rainy, dreary, dismal Friday to talk about indexes in SQL Server. But first, as part of my ongoing effort to produce high quality content for you lovely consumers out there, I solicited feedback about the audio and video quality of recent recordings. Apparently, the video quality is fine aside from the fact that I’m kinda ugly. And from the audio front, I got a couple pieces of feedback. One is, audio is better, not perfect, but much closer. No details on what got better, and what needs improving, but thank you anyway. And the other is, sounds a bit modulated. No details on what sounded a bit modulated, what modulation is, or how to fix that, but again, duly noted there. But anyway, this is the recorded video of a post that’s already written and scheduled. When, I forget, but it’s over on that computer, not on this computer, so it’s not in front of me. But you have that, you have that, you have that, you have that, you have that.
You have both versions to look forward to now, you lucky dogs. But this is all about how indexes talk to each other in SQL Server. I don’t mean like, like, you know, join two tables together and the indexes are like, hey, we can find the data. I mean like, indexes that live on the same table need to be able to talk to each other. Clustered indexes need to be able to talk to nonclustered indexes. And nonclustered indexes need to be able to talk to each other so that we can make our queries better and faster, and we get all the all the seeks in the world. We never see a scan because, Lord knows, we’d get calls from customers if they knew there were scans in our query plans. So I created two indexes on the users table. One of them is unique. You can tell that they are created by the helpful red underlining squiggles from IntelliSense. Thank you IntelliSense for your IntelliSense.
IntelliSense. But we have a unique index, which is noted both by the unique syntax in the definition and also by the underscore uc in the index name. Good there. That means that every value in the account ID column is unique. One before, one after, to the end of the table. Every value is unique. And now SQL Server knows it. And the non-unique index, well, that one just ends in NUC. No special syntax needed to note that it is not unique, thankfully.
So we’re going to run these two queries, and we’re going to talk about the query plans, and then we’re going to talk about why we got the query plans that we did. So the first one, which interrogates the uc unique index, is going to look a bit like so, where we have a predicate on the ID column, and then a seek predicate on the account ID column. Right? So we have a residual predicate to ID and a seek predicate to account ID. My meteorologist skills are slowly improving.
Strom Cods over Chickigo. If you remember that reference, you win one million dollars from beer gut magazine. Now the reason we got these two query plans, because when you have a table in SQL Server with a clustered index on it, it doesn’t have to be the primary key too, though usually that’s how it works out.
But when you have a table in SQL Server with a clustered index on it, every single time you create a nonclustered index, those nonclustered indexes will inherit, as part of their physical being, their structure, the clustered index key column, or columns, if you’re that kind of person. Where those columns end up depends on the definition of the index. By that I mean a unique index will carry the clustered index key column, or columns, in the includes.
I mean, the key of the index, which is an option for you. And then a non-unique nonclustered index will inherit the clustered index key column, or columns, in the key of the index. That’s why in the non-unique index query, we’re able to seek to both ID and account ID. And in the unique query, unique index query and query, we’re able to seek to account ID, but then residual predicate to ID.
Because ID is not in a part of the index that is seekable. It’s in the included section. In the bleacher seats, in the cheap seats. We can kind of expand on it, make it a little bit more clear what I mean there by adding another column to the index. So these index, I’m going to change both indexes to have account ID, then creation date, and then inherit the clustered index key column.
In this case, there’s only one. I don’t need to make everyone, I don’t need to make people with two clustered index key columns feel included on that, because I only have the one here. But you might have a different scenario at home, or at work, wherever you lay your head. So we’ve got these two indexes now, I think anyway, at least it seems like we do.
Didn’t get any errors, so I assume that’s right. But if I run both of those same queries again, the first one is going to be interrogating the uck, and the second one is going to be interrogating the nuk. The uck query plan is going to have the exact same details.
We have the predicate on ID, and the seek predicate on account ID. Nothing changed there. Boop. Boop. Why did that change? Well, because nonclustered index, sorry, rowstore indexes, not even non-clustered or cluster, rowstore indexes in SQL Server, have introduced sort of dependencies between their key columns.
So in this case, we’re able to seek to account ID, but then because we have creation date in the middle, right, you can kind of think of the index definition looking like this. I’m sorry about typing in demos, I’m sure someone will be deeply offended.
But if we changed our index definition to look like this, it becomes a little more clear why we had a seek to account ID, and then a residual predicate on ID, it’s because creation date is in the middle of that ID sandwich. Right? If we wanted to seek to ID, we would also need to search on creation date so that we wouldn’t have to, like, make that hop over creation date to find IDs.
That hop is a residual predicate or a range predicate. We have to hop over that thing and seek within that whole range of values. I’m going to get rid of that now so it doesn’t ruin future demos.
But we can kind of see how things work a little bit better if we do search all three columns, right? So we’re going to bang these two queries. And the first one is going to look about how we would expect it to look.
We have a predicate on ID still because in the unique clustered index, I’m sorry, in the unique nonclustered index, ID is still stuck in the includes, right? So ID is up there.
And then down at the bottom, we have account ID, and we also have creation date. And my meteorologist finger is going to get cut off while I try to point to creation date, but that’s okay. I didn’t need that finger anyway.
Probably all beer gut magazine money, I’m going to lose some more fingers in the process. So we can seek to account ID, seek to creation date, and then residual predicate to ID. All right?
But in the non-clustered, the non-unique nonclustered index, we have a triple seek. We seek to account ID, creation date, and ID. You can see account ID, creation date, and losing my finger there, but you can see ID a little bit over to the right, my disappeared finger.
Another way that indexes, non-clustered, well this time, nonclustered indexes can talk to each other in SQL Server. So if we create these two, both non-unique indexes. One of them is going to be on the last access date column, and one of them is going to be on the creation date column.
All right? So if we have those two indexes on our table, and we have a query that’s searching on those indexes, on those columns in those indexes, and we look at the query plan, we’re going to see something kind of interesting, where we have a seek into the creation date index, and then a seek into the last access date index.
And then those two indexes get joined together. What do they get joined on? You ready for a wild time?
The ID column. So SQL Server takes one index, joins it to the other index, and produces a result. Those are nonclustered indexes.
We use two nonclustered indexes together. There are different ways SQL Server can do that. There’s index intersection, index union, there’s all sorts of stuff SQL Server can do to bring two nonclustered indexes together. You’re probably more used to seeing a key lookup in a query plan.
I think if I quote out this, we should see that. And a key lookup is just a nonclustered index talking to the clustered index. All right?
So a key lookup is a little different from the index union or index intersection stuff. A key lookup is just saying, hey, nonclustered index, what’s in the clustered index? And then the clustered index says everything.
And then we have a key lookup. Anyway, that’s how indexes in SQL Server talk to, cooperate, communicate with each other, and how SQL Server’s really smart query optimizer is able to use indexes to do that, make all your queries super-duper fast, and yeah, I think that’s about it.
Anyway, it’s coming up on noon, which means it is lunchtime, and by lunch I mean red wine, and by red wine I mean I’m not even going to be using a cup today. So anyway, thanks for watching. Like and subscribe.
Smash that like button, as the kids say. Thanks for watching. Hope you learned something, and see you next time after the red wine wears off. Or maybe if it wears on a little too long. Heh heh.
Like, what a little bit. Bye. See you next time.
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.