Recommended Free Tools
The question “How can you tell which column should go first in an index?” has no answer that works without the query. In Brent Ozar’s September 3, 2026 article on the topic, the right key order depends on the predicates in the WHERE clause: whether each one is an equality, a range, or an inequality, and how much of the index each leading key lets the engine skip. Counting distinct values in the table is not enough.
Why the textbook answer breaks down
The familiar rule says to put the most selective column first, meaning the one with the most distinct values. Ozar’s article contests that answer. His argument is that the question “can’t be about the two columns in the table – it has to be about the filters in the query.” Two columns can have similar distinct counts and still need different index orders, because the filters applied to them behave differently.
As an Amazon Associate I earn from qualifying purchases.
The article illustrates this with SQL Server and the Stack Overflow dbo.Users table, which has DisplayName and Location columns. The rule of thumb is not wrong so much as incomplete. It does not say what the query asks the index to do.
Case 1: two equality predicates
The article starts with this query:
SELECT * FROM dbo.Users
WHERE DisplayName = 'alex' AND Location = 'Seattle, WA';Both predicates are equality searches. Ozar’s point is that, in this SQL Server example, either key order, (DisplayName, Location) or (Location, DisplayName), lets the engine seek on both values. Choosing between them does not change whether a seek is possible. Ranking the columns by distinct counts would produce an answer here, but the answer would not explain anything about the query.
#1 Best Overall
Case 2: one equality and one inequality
Ozar then changes the second predicate to an inequality:
SELECT * FROM dbo.Users
WHERE DisplayName = 'alex' AND Location <> 'Seattle, WA';Now the leading key matters. The article describes the two layouts this way:
Rank #2
- Careercup, Easy To Read
- Condition : Good
- Compact for travelling
DisplayNamefirst. The seek goes to the Alex entries. Within them, the engine reads rows on both sides of Seattle in location order and excludes the Seattle matches. The reads stay inside the set of rows named Alex.Locationfirst. An inequality on the leading key covers every location except Seattle, so the illustrated reads span people from many places regardless of name. The engine still has to checkDisplayNameon each entry it reads.
The article also notes that SQL Server may report the second access as an index seek, even though the amount of data read is close to what people informally call a scan. The operator label alone therefore does not show how much work was done. The article’s conclusion is blunt: “it’s really about which searches reduce your search space as quickly as possible.”
Quick wins for a faster PC:
Scan for outdated or missing drivers - takes under a minuteDriver Scan →Repair Windows errors before they cause bigger problemsFix Now →Fix the driver behind crashes, sound loss and screen glitchesFind Drivers →How to answer the interview question
A strong answer asks for the query before recommending anything. The sequence below is a paraphrase of Ozar’s approach, not a quotation from him.
Rank #3
- Ask for the query and its filters. Column definitions and table size come second.
- Classify each predicate. Mark each as equality, range, or inequality, and note the comparison values.
- Ask which leading key narrows the search fastest. A leading key that confines the seek to a small set of entries usually does more than one that forces a broad read.
- Check the plan. Compare the rows read against the rows returned, not just the operator names.
- Test against the real workload. Other queries that use the same table may pull the decision in another direction.
Rules like “equality columns always go first” fail for the same reason as the distinct-count rule. In Ozar’s inequality case, the equality column is the one that does the narrowing, but the answer still depends on the values and the operators in the actual query.
What the B-tree mechanics explain
Ozar’s companion article, “Database Animations: How Index Seeks Work,” published July 16, 2026, describes the mechanics behind these plans. The main points are:
- Root-to-leaf traversal. A seek starts at the root page and follows intermediate directory pages down to a leaf page. The leaf pages hold the actual index data.
- Key lookups. A nonclustered index can return keys that then require lookups in the clustered index to fetch the other columns. Those lookups add work that the seek operator does not show.
- Linked leaf pages. For ranges and scans, the engine can move sideways across linked leaf pages rather than traverse from the root each time. This explains why a wide range can read many entries even under a seek label.
These mechanics are the reason the first query and the second query can behave so differently. They describe SQL Server’s storage structure. They do not, on their own, establish how other database engines plan or execute the same query.
The Tool Desk
Outbyte PC Repair FREERepair Windows errors before they cause bigger problemsFix Now →Outbyte Driver Updater FREEFix the driver behind crashes, sound loss and screen glitchesFind Drivers →Limits of the example
- The article is an instructional illustration, not a benchmark. It does not report measured speedups, and it does not establish a universal rule for SQL Server or any other engine.
- The article’s comments include disagreement about selectivity and about how optimizers choose plans. Those debates are a reason to measure, not a substitute for measurement.
- Choosing a production index depends on the full query set, the plan, write costs, and maintenance overhead. A single example cannot settle that.
Where to go next
Ozar’s companion article points readers to his Fundamentals of Index Tuning 2026 recordings, which cover composite indexes and other tuning topics with animations. The article does not describe pricing or current offer terms for that course, so check the publisher’s site for those details before enrolling.
Verdict
The interview question is worth asking, but the answer starts with the query. Ask which predicates are equality, range, or inequality, which values they compare, and which key order confines the search most tightly. Then check the plan and the real workload before you commit to an index.
Article details: Brent Ozar Unlimited, September 3, 2026 (exact-title article, SQL Server examples). Companion article: “Database Animations: How Index Seeks Work,” July 16, 2026.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
