Database Animations: The Interview Question Everybody Gets Wrong

No no, I’m not just talking about the people answering the interview question.

I’m talking about the people asking the question.

Over and over, I see confident interviewers asking, “How can you tell which column should go first in an index?”

Then I see a confident candidate saying, “Check to see which column has the most distinct values, meaning it’s the most selective, and that column should go first.”

Those two people high-five each other, hug, kiss on the lips, and enter into a lasting, forever relationship, smug in the knowledge that they share the same values, when in fact those values are simply wrong. I mean, I’m not gonna judge what you’re into – I’m not here to kink-shame anybody – but even the furries know this is the wrong question and the wrong answer. (Furries out there, shout out, I love y’all.)

Let’s take the Users table from the Stack Overflow database, which has two string columns where people can type in whatever they want, and duplicates are allowed: DisplayName and Location. (Neither of those columns are unique – after all, there are a lot of people in the real world with the same fursonas.)

If we have this query:

Then which column should go first: DisplayName or Location? Right about now, some of the audience is scurrying off to query the number of distinct values in each column to see which one is more selective, but here’s the kicker:

They perform exactly the same. When you have two EQUALITY searches – that’s the key here – it doesn’t matter which column goes first. SQL Server can seek into the first value, and then seek into the second value, regardless of which column we put first.

Right about now, those same audience members who were checking unique values are screaming at the screen saying, “BUT BRENT, your example doesn’t include how the b-tree works! Surely the b-tree makes things different, right?!?” Nope, and here’s an illustration of that, too, going one index at a time since the b-tree pages take up a lot of screen resolution real estate:

When all you have is equality searches, it doesn’t really matter which column goes first in the index.

But when you have INequality searches…

Let’s change our query. Instead of looking for the Alexes who live in Seattle, let’s look for the Alexes who live anywhere OTHER than Seattle:

Now, suddenly column order matters a lot, as our next animation shall illustrate:

If we lead with DisplayName, then our query turns into two seeks:

• Seek to Alex, then the last Seattle value, and read forwards. The instant we hit a non-Alex DisplayName, our work is done.
• Seek to Alex, then the first Seattle value, and read backwards (everyone < Seattle.) Again, the instant we hit a non-Alex DisplayName, we’ve read too far backwards, we stop, and our work is done.

We only read the Alexes, of which there are a relatively limited number.

However, if we lead with Location, then we:

• Seek to the last Seattle, then read forwards. We read everyone who lives > Seattle, regardless of their name. That’s gonna be a lot of people.
• Seek to the first Seattle, then read backwards. We read everyone who lives < Seattle, regardless of name – again, a lot of people.

Bizarrely, SQL Server still calls this an index seek on the query plan because technically we know what values we’re jumping to. However, human beings like you and me (and the furries, they count as humans too, trust me) would call that a scan because of the sheer amount of data we’re reading.

So What’s the Right Question – and the Right Answer?

First off, the question can’t be about the two columns in the table – it has to be about the filters in the query. The filtering operators and the comparison values are what drives the answer.

Second, you would be forgiven for thinking that the takeaway is, “Equality searches should go first in the index, followed by the inequality searches.” After all, that’s how SQL Server’s missing index recommendations are generated. However, that isn’t the right answer either: it’s really about which searches reduce your search space as quickly as possible.

If you learned something in this post, read the rest of my free Database Animations first, and when you’ve absorbed those, check out my Fundamentals of Index Tuning class. I know you’ve been doing this stuff for years, but as you can see here, you never quite picked up the real fundamentals. It’s not your fault! Your old boss was too cheap to send you to a real training class, so you leaned on those free training videos you found on that MySpace archive. It’s time to level up, quit faking it, and stop hiding behind that awful distinct-values answer. (You can keep the furry mask on though.)

Note: I generated the animations in this post with Claude Code, but all of the text & demos are completely written by me.