When should you create an index?
Quick Answer
Index columns that appear in WHERE, JOIN, ORDER BY and GROUP BY clauses of your frequent, performance-sensitive queries — especially selective columns (those that filter to a small fraction of rows). Index foreign keys. Use composite indexes with the most selective / equality columns first. Don't index tiny tables, rarely queried columns, or heavily-written columns you don't filter on.
I index based on the queries the application actually runs. So: columns in WHERE clauses, join keys — especially foreign keys, which people forget — and columns used for ORDER BY or GROUP BY on big result sets. The column should be selective enough that the index actually narrows things down; indexing a bit flag on its own doesn't help. For composite indexes I lead with equality predicates and the most selective column, and I INCLUDE the columns the query returns so it doesn't have to do key lookups. I don't index tiny tables or columns that are written constantly but never filtered on. And I confirm with execution plans and the missing-index DMVs rather than guessing.
Good candidates:
- Columns in
WHEREpredicates of common queries, particularly equality filters and selective ranges. - Join columns — a missing index on a foreign key is a classic cause of slow joins.
- Columns used to sort or group large result sets.
- Columns that must be unique.
- Composite index when queries filter on the same set of columns together; put equality columns before range columns, most selective first.
INCLUDEthe columns the query returns but doesn't filter on, to make the index covering.
Poor candidates:
- Very small tables — a scan is already cheap.
- Low-selectivity columns on their own (e.g. a
bitflag) — the optimiser will scan anyway. A filtered index may help. - Columns updated constantly but never searched.
- Duplicating an existing index (same leading columns).
How to decide in practice: look at the actual execution plans and the missing-index DMVs (sys.dm_db_missing_index_details), the plan cache and Query Store, then add indexes for real workloads — not speculatively.
Practise the whole thread the way an interviewer walks it — each question builds on the last.