SQL Server Interview

Indexes & Query Performance

SQL Server interview questions covering indexes & query performance.

20 Questions Filter by level

Interview questions

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

20 Results
121
SeniorAdvanced

What is an index in SQL Server?

An index is a database structure that helps SQL Server locate rows more efficiently without scanning every row in a table. Conceptually, it is similar to an index in a book: it provides an ordered access path to data. Indexes can significantly improve SELECT, JOIN, filtering, and sorting performance. However, they consume storage and add maintenance cost to INSERT, UPDATE, and DELETE operations because SQL Server must keep index structures synchronized with the table.

122
SeniorAdvanced

What is a clustered index?

A clustered index determines the logical ordering of rows at the leaf level of the index, where the data pages themselves form the clustered index leaf level. Because a table's rows can be organized this way only once, a table can have only one clustered index. A clustered key is often used for range queries and primary access patterns. The clustered key should generally be narrow, stable, and preferably ever-increasing when that fits the workload, because it is also included as the row locator in nonclustered indexes.

123
SeniorAdvanced

What is a nonclustered index?

A nonclustered index is a separate B-tree structure containing indexed key values and row locators that allow SQL Server to find the underlying rows. A table can have multiple nonclustered indexes. They are commonly created on columns used frequently in WHERE clauses, JOIN predicates, ORDER BY operations, or selective lookups. Too many nonclustered indexes increase storage and write cost, so indexes should be designed around actual workload requirements.

124
SeniorAdvanced

What is the difference between clustered and nonclustered indexes?

A clustered index contains the table data at its leaf level and determines the table's clustered storage order. A nonclustered index is a separate structure containing index keys and a locator to the corresponding row. A table can have only one clustered index but multiple nonclustered indexes. Neither type is universally faster; performance depends on the query, key order, selectivity, included columns, data distribution, and workload.

125
SeniorAdvanced

What is a heap in SQL Server?

A heap is a table without a clustered index. Heap rows are not maintained in clustered-key order. Heaps are not inherently wrong and can be appropriate for some staging, ETL, or transient workloads. However, production OLTP tables often benefit from a suitable clustered index. Updates to variable-length heap rows can create forwarded records, which may increase I/O for later lookups.

126
SeniorAdvanced

What is a composite index?

A composite index contains more than one key column. The order of the key columns matters because SQL Server can efficiently use the leading portion of the index for many seek and ordering patterns. For an index on (CategoryId, Price), a query filtering CategoryId can often use it efficiently, and a query filtering both CategoryId and Price may also benefit. A query filtering only Price may not be able to use the same index as effectively for a seek because Price is not the leading key.

127
SeniorAdvanced

Why does column order matter in a composite index?

SQL Server's B-tree indexes are ordered by their key columns from left to right. The leading key columns therefore strongly influence which predicates can be used as efficient seek predicates and whether the index can satisfy an ORDER BY. Index key order should be based on actual query patterns, equality/range predicates, selectivity, sorting requirements, and workload—not on a simplistic rule such as always putting the most selective column first.

128
SeniorAdvanced

What are included columns in an index?

Included columns are non-key columns stored at the leaf level of a nonclustered index. They can allow an index to cover a query without increasing the logical key used for sorting and seeking. INCLUDE is useful for columns needed only in the SELECT list while other columns are used for filtering or joining. Included columns still consume storage and increase write maintenance, so they should be added selectively.

129
SeniorAdvanced

What is a covering index?

A covering index contains all columns required by a particular query—through its key columns, included columns, and implicitly available row locator—so SQL Server does not need to access the base table or clustered index for additional data. Covering indexes can substantially reduce I/O for important queries. However, creating a separate covering index for every query can lead to excessive index count and write overhead, so related workload patterns should be consolidated where practical.

130
SeniorAdvanced

What is a filtered index?

A filtered index is a nonclustered index built only on rows satisfying a WHERE predicate. It can be smaller and cheaper to maintain than indexing the entire table when queries repeatedly target a well-defined subset. Common examples include active rows, non-NULL values, unprocessed queue items, or a specific status. The query predicate must be compatible with the filter for SQL Server to use the index effectively.

131
SeniorAdvanced

What is index selectivity?

Selectivity describes how effectively a column or predicate narrows the number of matching rows. A highly selective predicate returns a relatively small fraction of the table. Indexes on highly selective access paths are often useful for seeks, but selectivity is not the only design factor. Join patterns, ordering, covering needs, data distribution, update frequency, and query frequency also matter. A low-selectivity column such as a BIT flag may still be useful as part of a filtered or composite index in the right workload.

132
SeniorAdvanced

What is an execution plan?

An execution plan shows how SQL Server intends to or actually executes a query. It contains physical operators such as Index Seek, Index Scan, Table Scan, Nested Loops, Hash Match, Sort, and Key Lookup. Estimated plans are generated without executing the query. Actual execution plans include runtime information after execution. Plans are essential for diagnosing performance because the SQL text alone does not reveal the optimizer's chosen access strategy.

133
SeniorAdvanced

What is the difference between an Index Seek and an Index Scan?

An Index Seek navigates an index to locate a targeted key range, while an Index Scan reads a larger portion or all of an index. A seek is often efficient for selective predicates, but a scan is not automatically bad. If a query needs a large percentage of a table, scanning can be cheaper than performing many seeks and lookups. The correct judgment comes from row counts, I/O, CPU, elapsed time, cardinality estimates, and the complete execution plan.

134
SeniorAdvanced

What is a Key Lookup?

A Key Lookup occurs when SQL Server uses a nonclustered index to identify qualifying rows but then needs additional columns from the clustered index that are not present in the nonclustered index. A small number of lookups can be efficient. A large number can become expensive because each qualifying row may require additional access. Possible solutions include changing the query, adding appropriate INCLUDE columns, redesigning the index, or accepting the lookup if the measured cost is low.

135
SeniorAdvanced

What is a SARGable query?

SARGable means a predicate can be used effectively as a search argument by an index. SARGable predicates often allow SQL Server to seek into an index rather than evaluate a function or expression for many rows. Applying a function to an indexed column can make a predicate non-SARGable. Rewriting the condition as a range often improves index usability.

136
SeniorAdvanced

What are statistics in SQL Server?

Statistics contain information about the distribution of values in columns and indexes. The query optimizer uses them to estimate how many rows different operations are likely to return. Accurate cardinality estimates are important because they influence join algorithms, memory grants, access methods, and operator choices. SQL Server can automatically create and update statistics when configured to do so, but large or unusual workloads may require targeted statistics maintenance and investigation.

137
SeniorAdvanced

What is index fragmentation?

Index fragmentation describes how index pages become less optimally organized as data changes. Logical fragmentation refers to pages being out of logical key order, while low page density means pages contain excessive free space. Fragmentation can matter for some scan-heavy workloads, but it should not be treated as the sole performance metric. Modern maintenance decisions should consider page count, workload, storage type, page density, and measured performance rather than rebuilding every index simply because a percentage crosses a fixed threshold.

138
SeniorAdvanced

What is the difference between REORGANIZE and REBUILD?

ALTER INDEX ... REORGANIZE performs an online, incremental defragmentation of leaf-level index pages and generally requires less resource at one time. ALTER INDEX ... REBUILD creates a new index structure and can more thoroughly address fragmentation and page density. Rebuild operations are more resource-intensive and can affect logging, locking, CPU, and I/O. Availability of online rebuild features depends on SQL Server version, edition, and index characteristics. Maintenance should be workload-driven rather than based only on generic percentage rules.

139
SeniorAdvanced

What is parameter sniffing?

Parameter sniffing occurs when SQL Server compiles a parameterized statement or stored procedure using parameter values available at compilation time and caches the resulting plan for reuse. This is normally beneficial, but it can cause unstable performance when different parameter values require very different plans because of skewed data distributions. Possible responses include improving indexes/statistics, rewriting the query, using OPTION (RECOMPILE) for appropriate cases, using OPTIMIZE FOR strategies, or applying newer SQL Server features such as Parameter Sensitive Plan optimization where available. The solution should be based on measured evidence rather than disabling plan reuse indiscriminately.

140
SeniorAdvanced

How do you troubleshoot a slow SQL Server query?

A systematic approach is more reliable than immediately adding indexes. First reproduce and measure the problem, then inspect the actual execution plan and runtime metrics. Check logical reads, CPU time, elapsed time, row-count estimates versus actual rows, scans, expensive lookups, sorts, spills, blocking, parameter sensitivity, implicit conversions, stale statistics, non-SARGable predicates, and missing or redundant indexes. Useful tools and techniques include the actual execution plan, SET STATISTICS IO, SET STATISTICS TIME, Query Store, Extended Events, DMVs, and controlled workload testing. The goal is to identify the actual bottleneck and fix its cause rather than optimizing based on assumptions.