Indexing interviews have a way of exposing the difference between someone who’s memorized “clustered vs non-clustered” and someone who’s actually spent time staring at execution plans deciding which columns belong where. Both candidates can define an index. Only one can explain why a specific covering index beats three overlapping narrower ones for a specific query pattern.
Split into Beginner (1–17), Intermediate (18–35), and Advanced (36–50), written the way you’d actually talk it through in a room — the reasoning behind each answer, not a textbook definition.
One framing worth carrying through the whole list: every indexing decision is a trade-off between read speed and write cost, and the honest answer to almost any “should I add this index” question starts with “it depends what else touches that table.” The strongest answers below make that trade-off explicit rather than treating indexes as a free performance upgrade.
Beginner Level
1. What is an index, and why does a database need one? A separate, ordered data structure built on one or more columns that lets the engine find rows quickly without reading the entire table — like a book’s index letting you jump to a page instead of reading cover to cover. Without the right index, even a simple filtered query forces a full table scan, which is fine on a small table and painful on a large one.
2. What’s the difference between a clustered and a non-clustered index? A clustered index defines the actual physical order the table’s data rows are stored in — there can only be one per table, since data can only be physically sorted one way. A non-clustered index is a separate structure that stores a copy of specific columns plus a pointer back to the actual data row, and a table can have many of them, each supporting a different query pattern.
3. Does every table need a clustered index? Not strictly — a table without one is called a heap, and it’s stored in no particular order. But in practice, having a clustered index is almost always beneficial, since it gives the table a defined physical organization that most access patterns (especially range queries and lookups by a common key like an ID) benefit from directly.
4. What is a primary key, and does it always create a clustered index? A primary key enforces uniqueness and non-nullability on a column (or columns), and by default in SQL Server/Azure SQL, creating a primary key also creates a clustered index on those columns — though this is a default behavior, not a hard rule, and you can explicitly choose to make a primary key non-clustered if a different column is a better clustering choice.
5. What’s the difference between a unique index and a primary key? Both enforce uniqueness, but a table can have only one primary key while it can have multiple unique indexes/constraints. A primary key also implies not-null, while a unique index can (depending on how it’s defined) allow a single null value, since null is treated as “unknown” and isn’t considered a duplicate of another null.
6. What is a composite (multi-column) index? An index built on more than one column, where column order matters — the index is effectively sorted by the first column, then the second within each value of the first, and so on. A composite index on (LastName, FirstName) efficiently supports searching by last name alone, or by last name and first name together, but not efficiently by first name alone.
7. What is a covering index? An index that contains every column a specific query needs — either as part of the index key or as an “included” column — so the engine can satisfy the entire query from the index itself, without having to go back to the actual table data at all.
8. What is a key lookup, and why is it considered a performance concern? When a non-clustered index doesn’t contain all the columns a query needs, the engine seeks the index to find matching rows, then does a separate lookup back into the table (or clustered index) for each matching row to retrieve the remaining columns. On a query returning many rows, that’s a lot of extra, often random I/O — usually a sign that a covering index would help.
9. What’s the difference between a table scan and an index scan? A table scan reads every row of the underlying table (a heap) sequentially. An index scan reads every entry of a specific index sequentially instead — narrower and often cheaper than a table scan since an index typically contains fewer columns, but still reading everything rather than seeking directly to what’s needed.
10. What’s the difference between a seek and a scan, and which is generally preferred? A seek uses an index’s sorted structure to jump directly to the matching rows, similar to using a book’s index to go straight to a page. A scan reads through rows (or index entries) sequentially, checking each one against the filter. Seeks are typically much cheaper when only a small fraction of rows match, though a scan can actually be more efficient when a large percentage of the table needs to be read anyway.
11. What are included columns in a non-clustered index, and how are they different from key columns? Key columns determine the index’s sort order and can be used for seeking, filtering, and sorting. Included columns are simply stored at the leaf level of the index for retrieval purposes, without affecting sort order or being usable for seeking — a common technique for making an index covering (adding the extra columns a query selects) without bloating or reordering the index’s key.
12. Does adding more indexes always make a database faster? No — every index speeds up reads for the specific patterns it supports, but adds overhead to every insert, update, or delete that touches the indexed columns, since the index itself has to be maintained alongside the data. More indexes isn’t automatically better; it’s a genuine trade-off that needs to match the actual read/write balance of the table.
13. What is index fragmentation? Over time, as data is inserted, updated, and deleted, an index’s physical pages can become disordered or partially empty, forcing the engine to read more pages than strictly necessary to retrieve the same data — degrading the efficiency the index was originally built to provide.
14. What’s the difference between reorganizing and rebuilding a fragmented index? Reorganize is a lighter-weight, always-online operation that defragments pages incrementally — appropriate for moderate fragmentation. Rebuild fully recreates the index from scratch, more thorough and effective for heavy fragmentation, and can run online in most editions, though it’s a heavier operation overall.
15. What is a missing index recommendation, and where do you find it in Azure SQL? A built-in feature that tracks queries which would have benefited from an index that doesn’t currently exist, surfaced via a DMV (sys.dm_db_missing_index_details) or through the portal’s Performance Recommendations blade — a helpful starting point for identifying indexing opportunities, though it’s an estimate-based suggestion, not an infallible directive.
16. Can Azure SQL create indexes for you automatically? Yes — Automatic Tuning’s CREATE INDEX option can automatically create indexes based on observed query patterns and missing index data, and can also automatically drop indexes (DROP INDEX option) that aren’t being used, continuously adapting the index footprint to actual workload patterns without manual intervention, if you enable it.
17. What’s a simple, practical first step when a query seems slow and you suspect an indexing issue? Look at the actual execution plan and check whether it’s doing a table/index scan where a seek would make more sense given the query’s filter — and check the missing index DMV or Query Store data for that specific query to see if the engine itself has already identified a specific gap, rather than guessing which index might help.
Intermediate Level
18. How do you decide the column order in a composite index? Generally, put the columns used in equality filters first, followed by columns used in range filters or sorting, and put columns that are only ever selected (not filtered or sorted on) as included columns rather than key columns — the goal is matching the index’s sort order to how the query actually filters and orders data, since a composite index is only useful for a leading-column-first pattern, similar to how a phone book sorted by last-then-first name doesn’t help you search by first name alone.
19. What’s the difference between index selectivity and cardinality, and why does selectivity matter for deciding whether an index will actually be used? Cardinality is the number of distinct values in a column; selectivity is the ratio of distinct values to total rows, essentially “how much does this column narrow things down.” A highly selective column (like an email address, nearly unique per row) is a great index candidate, since a seek on it returns very few rows. A low-selectivity column (like a boolean flag with only two possible values) is often a poor standalone index candidate, since a seek there still returns a large fraction of the table, at which point a scan might genuinely be cheaper.
20. How would you identify duplicate or heavily overlapping indexes on a table? Compare each index’s key column list and order — an index on (A, B) and another on (A, B, C) are often redundant, since the wider one can typically serve any query the narrower one could, making the narrower one a candidate for removal. sys.dm_db_index_usage_stats combined with reviewing sys.indexes/sys.index_columns lets you build this comparison systematically rather than eyeballing it.
21. What’s the trade-off consideration when deciding whether to widen an existing index (adding included columns) versus creating a new, separate covering index? Widening an existing index avoids the overhead of maintaining an entirely separate structure, but only makes sense if the existing index’s key column order already fits the new query’s filter pattern — if it doesn’t, forcing unrelated included columns onto an index whose key order doesn’t match just adds bloat without actually making it covering for the query that needs it, and a new, purpose-built covering index is the more correct answer.
22. How would you use sys.dm_db_index_usage_stats to inform an indexing decision, and what are its limitations? It shows seeks, scans, lookups, and updates per index since the last restart (or since the stat was last reset), letting you distinguish indexes that are genuinely earning their write-overhead cost through real read usage from ones that are just sitting there absorbing maintenance cost with no real read benefit. Its main limitation: it resets on certain events (and in Azure SQL Database, effectively on failover or certain maintenance events too), so a low-usage reading might just reflect a short observation window rather than genuinely low usage over the index’s whole lifetime — worth checking sys.dm_db_index_usage_stats‘s underlying stats reset time before drawing a firm conclusion.
23. What’s the difference between a filtered index and a regular index, and when would you use one? A filtered index includes only rows matching a specified WHERE predicate, rather than every row in the table — useful for a scenario like an IsActive or IsDeleted flag where queries almost always filter on the same condition, since the filtered index is smaller, cheaper to maintain, and more efficient specifically for queries whose filter matches the index’s predicate, at the cost of not being usable for queries that don’t share that same filter condition.
24. How does a columnstore index differ fundamentally from a traditional rowstore index, and when would you use one in Azure SQL? Columnstore indexes store data column-by-column rather than row-by-row, with heavy compression, and are optimized for large-scale analytical aggregation queries scanning many rows but relatively few columns — the opposite access pattern from OLTP point lookups, which rowstore indexes are optimized for. I’d use a columnstore index for a reporting/analytical workload on a large fact-table-style structure, not for a typical transactional table with frequent single-row lookups and updates.
25. What’s the impact of a wide clustered index key (many columns, or large data types) on non-clustered indexes? Every non-clustered index implicitly includes the clustered index’s key as its row-locator, since that’s how it finds its way back to the actual data row — a wide clustered key means every single non-clustered index on that table carries that overhead too, multiplied across however many non-clustered indexes exist, which is a strong argument for keeping a clustered index key narrow and stable, even though it’s a decision that’s easy to overlook since its cost is spread invisibly across every other index.
26. How would you approach index design for a table with a genuinely mixed read/write-heavy workload, where you can’t just optimize purely for reads? I’d be deliberately more conservative than I would on a read-heavy table — prioritizing a smaller set of well-designed, genuinely high-value covering indexes over a larger number of narrower, marginally-useful ones, reviewing actual usage stats regularly to prune anything not clearly earning its write-overhead cost, and specifically pushing back on adding a new index for a rarely-run query if the table’s write volume is already significant, since that trade-off tips differently on a write-heavy table than it would on a mostly-read one.
27. What’s the significance of index key size limits in SQL Server/Azure SQL, and how does that affect design choices? There’s a maximum size for an index key (900 bytes for the key itself in most default configurations), which matters when designing a composite index on wide columns — like multiple varchar or nvarchar columns — since exceeding that limit either prevents index creation outright or requires reconsidering which columns actually need to be key columns versus which could be included columns instead, since included columns don’t count against the same key size limit.
28. How would you handle indexing a table that’s frequently queried with a leading wildcard search (like LIKE '%searchterm%')? A standard B-tree index generally can’t efficiently support a leading-wildcard search, since the index’s sort order doesn’t help when you don’t know the starting characters — this is a case where a traditional index isn’t really the right tool, and I’d consider Full-Text Search (which Azure SQL Database supports) as the more appropriate solution for genuine free-text search patterns, rather than trying to force a B-tree index to do a job it’s not designed for.
29. What’s the interaction between index design and parameter sniffing — can a poorly designed index make parameter sniffing issues worse? Yes — if an index’s selectivity varies dramatically depending on the specific parameter value used (an index that’s highly selective for a rare value but not very selective for a common one), the optimizer’s plan choice for that query becomes more sensitive to which parameter value it happened to compile against, making parameter sniffing symptoms more pronounced. A more thoughtfully designed index, or in some cases accepting that a single index can’t serve a genuinely bimodal selectivity pattern well, is part of a complete fix alongside any plan-level mitigation.
30. How would you evaluate whether Automatic Tuning’s CREATE INDEX suggestions are actually good, before letting them apply automatically? I’d review the specific query pattern the recommendation targets against Query Store data — how frequently that query actually runs and how much it would genuinely benefit — cross-check it against existing indexes for redundancy before assuming it’s not already partially covered, and consider the write overhead implication for that specific table given its actual write volume, rather than treating every recommendation as automatically correct just because the system estimated a benefit.
31. What’s the difference between index maintenance needs on a Hyperscale database versus General Purpose or Business Critical? The fundamental fragmentation and maintenance concepts are the same, but Hyperscale’s distributed storage architecture changes some of the underlying I/O characteristics — worth validating that your usual reorganize/rebuild thresholds and scheduling still make sense at the scale Hyperscale databases are typically used for, rather than assuming identical maintenance timing and impact as a smaller General Purpose database.
32. How would you approach index design differently for a heavily partitioned table versus a non-partitioned one? I’d consider whether indexes should be aligned with the partitioning scheme (partitioned the same way as the table) versus non-aligned, since aligned indexes let partition-level maintenance operations (like rebuilding a single partition) stay efficient and isolated, while a non-aligned index spanning all partitions loses that benefit — the choice depends on whether your maintenance and query patterns are genuinely partition-aware or if you need cross-partition index support that an aligned index wouldn’t efficiently provide.
33. What’s your approach to indexing foreign key columns, and is it always necessary? Foreign key columns are frequently used in join predicates, so indexing them is often beneficial for join performance — but it’s not an automatic, unconditional rule; if a foreign key column is rarely used in a join or filter in practice, or if it’s already covered as a leading column in another index for a different reason, adding a dedicated index purely because “it’s a foreign key” without checking actual usage is exactly the kind of reflexive indexing that adds write overhead without a corresponding read benefit.
34. How do you approach evaluating whether a large existing index is actually still needed as the application and query patterns evolve over time? I’d rely on genuine usage statistics (seeks/scans/lookups versus updates) gathered over a meaningful, representative period rather than a snapshot, cross-referenced against whether the application feature or report that originally justified the index still exists and is actively used — since an index built for a feature that was later deprecated or replaced is a common, easy-to-miss source of indefinitely lingering write overhead nobody remembers to clean up.
35. What’s a scenario where removing an index, rather than adding one, is the correct performance fix? When a table has accumulated multiple overlapping or genuinely unused indexes over time — often from different developers independently adding indexes to fix their own specific slow query without checking what already existed — removing the redundant ones reduces write overhead and can, somewhat counterintuitively, actually improve overall performance on a write-heavy table more than any single additional index would have, since the cumulative maintenance cost of excess indexes was itself the bottleneck.
Advanced Level
36. Design an indexing strategy for a high-throughput OLTP table (millions of writes per day) that also needs to support a handful of critical, frequently-run reporting queries. I’d keep the clustered index narrow and stable (an identity or sequential key, avoiding a wide or frequently-changing clustering key given the write volume), maintain a genuinely minimal set of non-clustered indexes directly supporting the OLTP access patterns, and specifically route the reporting queries to a read-only secondary via read scale-out if the tier supports it — letting the reporting-specific indexes exist on a workload-isolated read path rather than adding their write overhead to the primary’s already-heavy OLTP write volume. If read scale-out isn’t available, I’d very deliberately evaluate whether each reporting index’s read benefit genuinely outweighs its write cost on the primary, rather than assuming the reporting need automatically justifies the index.
37. Explain the internal B-tree structure of a non-clustered index and how understanding it informs a decision about included columns versus key columns. A B-tree index has key columns determining the tree’s navigation structure (root, intermediate, and leaf pages sorted by key), while included columns exist only at the leaf level, adding no navigational cost to the tree’s depth or seek efficiency — meaning you can add a meaningful number of included columns to make an index covering without materially affecting seek performance, but adding those same columns as key columns instead would widen every level of the tree, increasing the index’s overall size and page count unnecessarily. This structural understanding is exactly why “key column when it drives access/sort, included column when it’s just needed for the result” isn’t just guidance, it’s a direct consequence of how the underlying structure actually works.
38. How would you approach indexing strategy for a table using Always Encrypted on some columns, given the query restrictions that come with it? Standard Always Encrypted columns (without secure enclaves) can’t be efficiently used in range queries, and equality comparisons require a deterministic encryption type specifically to remain seekable at all — meaning an index on a randomized-encryption column is effectively unusable for filtering, only for coverage of a SELECT. I’d carefully choose deterministic encryption specifically for columns that genuinely need to remain indexable/searchable, understanding the reduced security guarantee that comes with deterministic versus randomized encryption, and treat that as an explicit trade-off decision made with the security team, not purely a DBA indexing choice made in isolation.
39. What’s your approach to index design when a table needs to support both point-in-time historical queries (temporal tables) and current-state OLTP queries efficiently? System-versioned temporal tables maintain a separate history table automatically, and I’d design indexes on the history table independently from the current table’s indexes, since their access patterns are typically quite different — current-state queries are usually point lookups or narrow ranges by primary key, while historical queries often filter by a time range across potentially the whole history — rather than assuming the same indexing strategy that serves the current table well automatically also serves the history table’s genuinely different query shape.
40. Explain how you’d diagnose and resolve an index design problem where two indexes are both individually well-justified by usage stats, but together are causing a specific write-heavy table to struggle under peak load. I’d look at whether the two indexes can be consolidated into a single, slightly wider covering index that serves both original query patterns, rather than assuming both need to remain as fully separate structures — since usage stats alone tell you an index is being read, not that it’s the most efficient way to satisfy that read, and two indexes that are each individually justified can still represent an inefficient combined design compared to one well-designed wider index doing the same overall job with less total maintenance overhead.
41. How would you approach indexing for a table using Row-Level Security, given the security predicate itself is effectively an implicit filter on every query? I’d make sure the tenant/security-predicate column is a leading key column on the most heavily-used indexes for that table, since every query against it implicitly includes that filter regardless of what the query itself asks for — an index that doesn’t lead with the RLS predicate column forces the engine to apply the security filter less efficiently (often after a broader scan or seek than would otherwise be needed), effectively making RLS’s filtering the single most important, universal access pattern to design around for that table.
42. What’s your approach to evaluating whether a columnstore index or a traditional rowstore covering index is the better fit for a borderline analytical workload — not obviously OLTP, not obviously a huge fact table? I’d look at the actual query pattern’s column-to-row ratio and aggregation behavior — queries that aggregate over many rows but touch relatively few columns, on a table large enough that compression and column-elimination benefits genuinely materialize, favor columnstore; queries that need many columns per row, or operate on a table too small for columnstore’s overhead to pay off, favor a well-designed rowstore covering index instead — and I’d validate empirically with real representative data rather than deciding purely from general guidance, since the actual breakeven point genuinely depends on table size and specific query shape.
43. How would you handle a scenario where Automatic Tuning keeps creating and then reverting the same index recommendation repeatedly over time? This pattern suggests the recommendation’s estimated benefit is genuinely marginal or highly workload-dependent — beneficial during certain periods or query patterns, not beneficial (or actively costly) during others — rather than a clear, stable win. I’d manually investigate the specific query pattern driving the recommendation and consider whether a more targeted, purpose-built index (rather than whatever generic shape the automated recommendation is proposing) would actually resolve the underlying need more decisively, or whether the workload’s variability itself is the real issue that no single index choice fully resolves.
44. Explain the trade-offs of using an index with INCLUDEd columns that themselves are large data types (like a wide varchar or nvarchar(max)). Included columns don’t affect the B-tree’s navigation structure, but they do still consume physical storage at the leaf level and are still copied and maintained on every insert/update/delete touching the table — a very wide included column can meaningfully bloat the index’s overall size and I/O footprint even without affecting seek depth, which is a trade-off worth weighing against simply accepting a key lookup for that specific wide column in cases where it’s rarely actually needed by the queries the index otherwise serves well.
45. How would you approach index strategy for a sharded, multi-tenant database architecture where individual shard sizes vary dramatically (some tenants much larger than others)? I’d avoid assuming a single indexing strategy validated against a mid-sized shard automatically holds for both the smallest and largest shards — a very large tenant’s shard might genuinely benefit from an index that would be pure overhead on a tiny tenant’s shard with too little data for the optimizer to ever prefer a seek over a scan anyway. I’d specifically validate indexing decisions against representative shards at both ends of the size distribution, not just an average-sized one, since a one-size-fits-all schema deployed identically across wildly different shard sizes can genuinely be suboptimal at either extreme.
46. What’s your approach to reasoning about the interaction between statistics and indexes — can a perfectly designed index still perform poorly due to statistics issues? Yes, and this is a common trap — an index can be structurally ideal for a query’s access pattern, but if the statistics on the indexed column are stale or the optimizer’s cardinality estimate is wrong for another reason (a correlated column the optimizer doesn’t know is correlated, for instance), the optimizer might not choose to use that index efficiently, or might choose a poor join order around it, even though the index itself is fine. I’d always confirm statistics are current and cardinality estimates are reasonable before concluding an index redesign is needed, since sometimes the actual fix is a statistics update, not an index change at all.
47. How would you handle designing indexes for a table that needs to support both MERGE statement-based upserts and traditional SELECT query patterns efficiently? MERGE operations typically need to efficiently match rows against a source set, which usually benefits from the same kind of index that would support a straightforward equality-based SELECT on the matching key — but I’d specifically test the MERGE operation’s own execution plan, not just assume a SELECT-optimized index automatically serves the MERGE‘s internal join efficiently, since MERGE plans have their own particular optimizer behavior and edge cases worth validating directly rather than by analogy.
48. Explain how you’d approach an index redesign project on a large, long-lived production table where nobody currently fully understands why several existing indexes were originally created. I’d start from genuine usage data (seeks/scans/lookups/updates, and ideally a longer observation window than the default reset-prone stats provide, perhaps via periodically snapshotting the DMV data over weeks) rather than trusting index names or assumed intent, cross-reference against Query Store to see which real queries are actually using each index, and propose changes incrementally with a clear rollback plan for each one rather than a single large redesign — since a big-bang change to a table nobody fully understands the history of carries meaningfully more risk than a series of smaller, individually validated adjustments.
49. How would you evaluate whether an index consolidation effort (merging several overlapping indexes into fewer, wider ones) is likely to be a net win before implementing it broadly? I’d pilot the consolidation against a representative test environment using real captured production query patterns from Query Store, comparing before/after plans and resource consumption for the actual queries that used to rely on the now-removed narrower indexes — specifically checking that none of them regress due to losing a narrower index’s more precise selectivity or slightly different sort order — before rolling the consolidation out broadly, since a consolidation that helps write overhead but silently regresses one previously-fine query is a real and easy-to-miss risk of this kind of change.
50. Walk through a genuinely difficult indexing challenge you’ve handled or would expect to handle, end to end. Intentionally open-ended, because interviewers are evaluating judgment and process, not a memorized script. A strong structure to demonstrate regardless of the specific scenario: (1) establish the actual problem with real data — usage stats, Query Store, execution plans — rather than guessing which index is “obviously” needed or unneeded, (2) weigh the read benefit against the genuine write cost for that specific table’s actual workload balance, not indexing in the abstract, (3) prefer the narrowest effective change — widening an existing index over adding a new one, consolidating over accumulating, (4) validate the change against representative data and real query patterns before applying broadly, and (5) confirm the change actually delivered the expected outcome afterward using the same objective data sources, rather than assuming success. Interviewers consistently favor candidates who describe the trade-off explicitly — what this index costs, not just what it helps — over candidates who only ever describe indexes as something to add.
A Closing Thought
The idea running through nearly every advanced answer here: indexing is never a purely additive decision — every index you create is a promise to maintain it on every future write, and the best indexing strategies are the ones that stay honest about that cost, not just chase every read-side win in isolation.
Discover more from Technology with Vivek Johari
Subscribe to get the latest posts sent to your email.
