SQL Server Interview

Advanced Indexing

SQL Server interview questions covering advanced indexing.

20 Questions Filter by level

Interview questions

Open a question for the full answer, code and interview guidance.

20 Results
221
SeniorAdvanced

How is a SQL Server B-tree index structured?

Traditional rowstore indexes in SQL Server are organized as balanced tree structures with a root level, zero or more intermediate levels, and a leaf level. SQL Server can navigate from the root through intermediate pages to locate the required leaf pages efficiently. In a clustered index, the leaf level contains the table's data rows. In a nonclustered index, the leaf level contains index rows plus a row locator to the underlying data. Understanding this structure explains why key order, page splits, index depth, and covering strategies affect performance.

222
SeniorAdvanced

What characteristics make a good clustered index key?

A useful clustered key is commonly narrow, stable, unique or made unique internally, and chosen to support important access patterns. An ever-increasing key can reduce random page splits for many insert-heavy OLTP workloads. Because the clustered key is carried as the row locator in nonclustered indexes, a very wide clustered key increases the size of those indexes as well. There is no universal rule that every clustered key must be an IDENTITY integer. The correct choice depends on query patterns, insert behavior, uniqueness, storage cost, and data lifecycle.

223
SeniorAdvanced

What happens when a clustered index key is not unique?

SQL Server allows a non-unique clustered index. When duplicate clustered-key values exist, SQL Server can add an internal uniqueifier to distinguish rows. That uniqueifier can increase storage requirements because the clustered row locator is used by nonclustered indexes. If a naturally unique, narrow clustered key is available, it can avoid this overhead, but uniqueness is only one factor in clustered-index design.

224
SeniorAdvanced

What is a page split?

A page split can occur when SQL Server needs to insert or enlarge a row on an index page that does not have enough free space. SQL Server may allocate another page and redistribute rows between pages. Frequent page splits can increase logging, I/O, fragmentation, and write cost. Random inserts into the middle of a heavily populated index are a common cause. Possible responses include choosing a more suitable key, reviewing fill factor, reducing row width, and measuring whether splits are actually a material workload problem.

225
SeniorAdvanced

What is fill factor?

Fill factor controls how full SQL Server makes leaf-level index pages when an index is created or rebuilt. A fill factor below 100 leaves free space that can accommodate future inserts or row expansion. A lower fill factor may reduce page splits in some workloads but increases index size, memory consumption, and I/O because fewer rows fit on each page. Fill factor should therefore be selected from measured modification patterns. Setting a low value globally is usually not a good tuning strategy.

226
SeniorAdvanced

What is index depth?

Index depth is the number of B-tree levels SQL Server traverses from root to leaf. As an index grows, additional intermediate levels may be required. A deeper index can require more page accesses for navigation, although SQL Server caches frequently used upper-level pages effectively. Narrow keys allow more index entries per page and can help keep structures smaller. Index depth should be interpreted together with workload, page count, cache behavior, and query performance rather than treated as an isolated tuning target.

227
SeniorAdvanced

Why are narrow index keys generally preferred?

Narrow keys allow more index entries to fit on each 8 KB page, reducing storage and potentially reducing the number of pages SQL Server must read. This is particularly important for clustered keys because clustered-key values are included as row locators in nonclustered indexes. A wide clustered key can therefore multiply storage cost across the database. However, an index key must first support the required access pattern; minimizing width should not destroy useful query ordering or selectivity.

228
SeniorAdvanced

What is the difference between key columns and INCLUDE columns?

Key columns define the logical ordering of a nonclustered index and can participate directly in seek and ordered-access behavior. INCLUDE columns are stored only at the leaf level and do not determine the B-tree's key ordering. Columns needed for WHERE, JOIN, or ORDER BY may belong in the key depending on the query pattern. Columns needed only to return data can often be placed in INCLUDE to create a covering index without unnecessarily widening the key. Included columns still consume storage and write resources.

229
SeniorAdvanced

How do you decide the order of columns in a composite index?

Column order should reflect the workload. Important factors include equality predicates, range predicates, join columns, required ordering, grouping, selectivity, and whether the query needs to use a useful leftmost prefix. For example, if a frequent query filters by CustomerId and then a date range, an index on (CustomerId, OrderDate) may provide an efficient access path. The simplistic rule 'put the most selective column first' is not always correct because sorting and predicate structure can be more important.

230
SeniorAdvanced

Can SQL Server use a composite index if the first key column is not filtered?

It can sometimes use the index, but it may not be able to perform an efficient seek based solely on a later key column. For an index on (CustomerId, OrderDate), a query filtering only OrderDate may scan the index or use another access method because the index is primarily ordered first by CustomerId. SQL Server may still choose the index if it is narrower or otherwise cheaper than alternatives, but this does not mean the later key has the same seek capability as the leading key.

231
SeniorAdvanced

What is an index intersection?

Index intersection occurs when SQL Server uses multiple indexes on the same table and combines their results to satisfy a query. For example, one index may locate rows by CustomerId and another by StatusId, with the optimizer combining qualifying row locators. This can be useful, but a well-designed composite or covering index may be more efficient for a critical repeated query. The actual execution plan and workload should determine whether redesign is justified.

232
SeniorAdvanced

What is an index spool?

An Index Spool is an execution-plan operator that creates a temporary indexed worktable, usually in tempdb, so intermediate data can be accessed efficiently multiple times during query execution. Spools can be beneficial optimizer strategies, but an expensive repeated spool may indicate that the permanent indexing strategy or query structure deserves investigation. A spool should not automatically be considered a problem; inspect its cost, row counts, rewinds/rebinds, and the surrounding operators.

233
SeniorAdvanced

What are missing-index recommendations?

SQL Server can record information about indexes the optimizer believes might reduce the estimated cost of queries. Missing-index suggestions can appear in execution plans and dynamic management views. They are useful clues, not commands. Suggestions are generated from individual optimization opportunities and do not fully account for existing overlapping indexes, write overhead, storage, or the complete workload. Before creating a recommended index, compare it with existing indexes and consider consolidating similar recommendations.

234
SeniorAdvanced

How do you identify unused or rarely used indexes?

The sys.dm_db_index_usage_stats DMV records operations such as user seeks, scans, lookups, and updates for indexes since the relevant statistics were initialized. An index with many writes but no observed reads may be a removal candidate, but the observation window matters. The DMV can reset after restart, failover, detach/attach, and other events. Before dropping an index, verify that it is not needed by infrequent monthly, quarterly, reporting, maintenance, or emergency workloads.

235
SeniorAdvanced

What are duplicate and overlapping indexes?

Duplicate indexes have effectively the same key definition and often the same coverage. Overlapping indexes share substantial leading key structure or coverage but are not exact duplicates. For example, indexes on (CustomerId) and (CustomerId, OrderDate) INCLUDE (TotalAmount) may overlap depending on workload. Consolidating redundant indexes can reduce storage and modification cost, but do not drop an index based solely on similar column names. Compare key order, INCLUDE columns, uniqueness, filters, usage, and query plans.

236
SeniorAdvanced

What is a columnstore index?

A columnstore index stores data by column rather than primarily by row, using compressed column segments and batch-oriented processing. It is designed especially for analytical workloads that scan and aggregate large numbers of rows while reading relatively few columns. SQL Server supports clustered and nonclustered columnstore indexes. Columnstore can dramatically improve data warehouse and reporting workloads, while modern versions also support operational analytics scenarios. It should be selected according to workload characteristics rather than used as a replacement for every rowstore index.

237
SeniorAdvanced

What is the difference between rowstore and columnstore indexes?

Rowstore indexes organize data by rows and are highly suitable for OLTP patterns such as point lookups, small range queries, and frequent transactional modifications. Columnstore organizes values by columns, provides high compression, and enables efficient batch-mode processing for large scans and aggregations. Many systems use both technologies: rowstore for transactional access and columnstore for analytics. The appropriate design depends on read patterns, modification rates, latency requirements, and SQL Server version.

238
SeniorAdvanced

What is an indexed computed column?

An eligible computed column can be indexed so SQL Server can efficiently search or order by a derived expression. This can be useful when applications repeatedly filter by a deterministic calculation that would otherwise require computing a function for many rows. SQL Server imposes determinism, precision, SET-option, ownership, and data-type requirements for indexing computed columns. Before adding one, verify that the expression and workload justify the extra storage and maintenance.

239
SeniorAdvanced

When should an index be dropped?

An index may be a candidate for removal when it is redundant, unused over a representative workload period, disproportionately expensive to maintain, or no longer supports current application queries. Dropping indexes should be evidence-based. Review usage statistics, Query Store, execution plans, index definitions, maintenance cost, and business reporting cycles. Indexes that enforce PRIMARY KEY or UNIQUE constraints have correctness implications and must not be treated like ordinary optional performance indexes.

240
SeniorAdvanced

A table has many indexes but INSERT and UPDATE operations are slow. How would you troubleshoot it?

First measure the write workload and inspect all indexes on the table. Every relevant INSERT, DELETE, and key/included-column UPDATE may require SQL Server to maintain multiple index structures. Use index usage statistics to identify indexes with high user_updates but little demonstrated read value. Look for duplicate or overlapping indexes, unnecessarily wide INCLUDE lists, poor clustered-key design, page splits, foreign-key requirements, triggers, constraints, and transaction blocking. Then evaluate read and write workloads together before consolidating or removing indexes. The correct objective is not the fewest indexes possible; it is the smallest set of indexes that provides required read performance without imposing unjustified write and storage cost.