iTechGuides is reader-supported. When you buy through links on our site, we may earn an affiliate commission. As an Amazon Associate I earn from qualifying purchases. Learn more
The question “How can you tell which column should go first in an index?” cannot be answered from the table definition alone. Brent Ozar’s September 3, 2026 article argues that the answer depends on the query’s filters: which predicates are equality tests, which are inequalities, what values they compare, and which leading key reduces the search space fastest. In his SQL Server example, two equality conditions can be served by either key order, but changing one condition to an inequality makes the leading key matter.
Why the usual answer fails
The familiar reply is to put the column with the most distinct values first. Ozar’s article contests that reasoning as a complete answer. His opening point is direct: “First off, the question can’t be about the two columns in the table – it has to be about the filters in the query.” A column’s distinct count says something about its values, but it says nothing about which rows a particular statement will ask for.
The reverse shortcut is also incomplete. “Equality columns go first” is a tidy rule, but the article does not treat it as the whole answer either. The safer habit is to start from the query and work outward.
The worked example
The article uses the Stack Overflow dbo.Users table, which has DisplayName and Location columns, and begins with this query:
#1 Best Overall
SELECT * FROM dbo.Users
WHERE DisplayName = 'alex' AND Location = 'Seattle, WA';Both predicates are equality searches. According to Ozar, the key order does not change the engine’s ability to seek on each value in this case. An index on DisplayName, Location or on Location, DisplayName can locate the matching rows either way.
When one predicate becomes an inequality
The article then changes the second filter to Location <> 'Seattle, WA'. The order now matters:
Rank #2
- Careercup, Easy To Read
- Condition : Good
- Compact for travelling
- DisplayName first. The seeks can stay within the rows for people named Alex, although the engine must read index entries on both sides of Seattle inside that band.
- Location first. The illustrated reads can cover people across many locations regardless of name, so the engine touches far more entries before the name test narrows the result.
Ozar notes that SQL Server may still label the second access an index seek, even when the amount of data read looks like what people informally call a scan. An operator name therefore does not tell you how many entries were read. His conclusion is that the goal is “which searches reduce your search space as quickly as possible.”
Do these 3 things before closing this tab:
1Fix the driver behind crashes, sound loss and screen glitches2Repair Windows errors before they cause bigger problems3Scan for outdated or missing drivers - takes under a minuteHow to answer the interview question
The article suggests a response that asks for the query before offering an opinion. The wording below is a paraphrase of the article’s approach, not a quotation from the author:
Rank #3
- Ask to see the query, including its
WHEREclause and any joins orORDER BY. - Classify each predicate as equality, range, or inequality, and note the literal values it compares.
- For each candidate key order, ask which leading key confines the search to the fewest rows.
- Check the actual execution plan and the reads it reports, not only the operator name, before choosing an index.
In SQL Server, you can compare logical reads for each candidate with SET STATISTICS IO ON before and after creating the index, against representative data.
What the B-tree animations add
Ozar’s companion article, “Database Animations: How Index Seeks Work,” published July 16, 2026, explains the mechanics behind these choices. A seek starts at the root page of the B-tree, follows intermediate directory pages, and reaches a leaf page. As he puts it, “The pages with the actual data are called leaves.”
Two further mechanics matter for the example. A nonclustered index can return keys that then require clustered-index key lookups to fetch the remaining columns, so a cheap seek can still generate substantial follow-up work. For ranges and scans, the engine can traverse linked leaf pages in order. Both points explain why the same seek-looking plan can read very different amounts of data depending on the leading key and the predicate.
Limits of the example
- The article is an instructional illustration in SQL Server, not a benchmark. It does not measure a universal speedup or establish how another database engine would behave.
- It does not establish a production index recommendation. The right index depends on the real query mix, data distribution, plan choices, write load, and maintenance cost.
- The article’s comments include disagreement about selectivity and the role of the optimizer. Treat them as discussion, not as evidence that replaces testing.
The practical lesson for an interview, or for a design review, is to replace the column-order question with a query-order question and then verify the answer on your own workload.
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.
