SQL Server Interview Questions & Answers

Topic 6: Indexes & Statistics

Questions 53–62 | Beginner to Intermediate

This section covers SQL Server indexes, fragmentation, index maintenance, covering indexes, Key Lookups, Index Seeks, Index Scans, included columns and statistics.

Indexes and statistics are closely related to SQL Server query performance. A well-designed indexing strategy can significantly reduce I/O, while accurate statistics help the Query Optimizer estimate row counts and choose efficient execution plans.

53. What is an index?

Answer:
An index is a database structure designed to help SQL Server locate rows efficiently without having to examine every row in a table.

Indexes are similar to the index of a book. Instead of reading every page to find a topic, the index helps identify where the required information is located.

Common SQL Server index types include:

Clustered indexes
Nonclustered indexes
Unique indexes
Filtered indexes
Columnstore indexes
XML indexes
Spatial indexes

Example:

CREATE NONCLUSTERED INDEX IX_Orders_CustomerID
ON dbo.Orders(CustomerID);

Interview Tip:
Indexes can improve read performance, but they are not free. They require storage and must also be maintained when data changes.

54. Clustered vs nonclustered index?

Answer:
A clustered index defines the logical ordering of the table's data rows at the leaf level of the clustered B-tree.

Because the table's data rows form the leaf level of the clustered index, a table can have only one clustered index.

A nonclustered index is a separate B-tree structure containing index keys and row locators that identify the corresponding table rows.

Clustered Index

The leaf level contains the actual table data rows.
A table can have only one clustered index.
The clustered index key should generally be chosen carefully.

Nonclustered Index

Stored separately from the base table or clustered index.
A table can have multiple nonclustered indexes.
Contains index keys plus row locators.
Can contain included columns.

Interview Tip:
Avoid saying that a clustered index permanently physically sorts the table on disk. It maintains logical key ordering through its B-tree structure.

55. What is index fragmentation?

Answer:
Index fragmentation describes conditions where index pages are no longer arranged as efficiently as expected for the workload.

Two commonly discussed forms are:

Logical Fragmentation

Logical fragmentation occurs when the logical order of index pages does not match their physical sequence.

Low Page Density

Index pages can also contain significant unused space. This can cause SQL Server to read more pages than necessary when processing a query.

Fragmentation can be influenced by:

Page splits
Random inserts
Updates that increase row size
Deletes
Index key design
Fill factor configuration

Index physical statistics can be inspected using:

SELECT
    OBJECT_NAME(object_id) AS TableName,
    index_id,
    avg_fragmentation_in_percent,
    avg_page_space_used_in_percent,
    page_count
FROM sys.dm_db_index_physical_stats
(
    DB_ID(),
    NULL,
    NULL,
    NULL,
    'LIMITED'
)
WHERE index_id > 0
ORDER BY avg_fragmentation_in_percent DESC;

DBA Tip:
Do not rebuild every index simply because fragmentation exists. Consider page count, workload, page density, query patterns and the actual performance benefit before performing maintenance.

56. Rebuild vs reorganize?

Answer:
Both operations are used for index maintenance, but they work differently.

ALTER INDEX ... REORGANIZE

Incrementally reorganizes leaf-level index pages.
Uses an online operation.
Generally requires less additional working space than a rebuild.
Does not automatically update statistics with a full scan.

ALTER INDEX IX_Orders_CustomerID
ON dbo.Orders
REORGANIZE;

ALTER INDEX ... REBUILD

Rebuilds the index structure.
Can reduce fragmentation and improve page density.
Requires more resources than reorganize.
Updates index statistics as part of the rebuild.
Can generate significant transaction log activity.

ALTER INDEX IX_Orders_CustomerID
ON dbo.Orders
REBUILD;

Interview Tip:
You may hear rules such as "reorganize between 5% and 30% and rebuild above 30%." These are traditional starting guidelines, not universal rules. Modern index maintenance should also consider index size, workload, page density and whether fragmentation is actually affecting performance.

57. What is a covering index?

Answer:
A covering index contains all columns required by a query so SQL Server can satisfy the query directly from the index without accessing the underlying table or clustered index for additional columns.

Consider this query:

SELECT
    OrderDate,
    TotalAmount
FROM dbo.Orders
WHERE CustomerID = 1001;

A covering index could be:

CREATE NONCLUSTERED INDEX IX_Orders_CustomerID
ON dbo.Orders(CustomerID)
INCLUDE (OrderDate, TotalAmount);

The index contains the search column and the additional columns required by the query.

DBA Tip:
Covering indexes can reduce I/O and eliminate expensive lookups, but making every index cover every query can create excessive storage and DML overhead.

58. What is a Key Lookup?

Answer:
A Key Lookup occurs when SQL Server uses a nonclustered index to locate rows but needs additional columns that are not available in that index.

SQL Server then uses the clustered index key stored in the nonclustered index to retrieve the missing columns from the clustered index.

Simplified execution pattern:

Nonclustered Index Seek
        ↓
Matching Row Found
        ↓
Required Column Missing
        ↓
Key Lookup
        ↓
Retrieve Remaining Columns
from Clustered Index

A small number of Key Lookups may be perfectly acceptable.

However, thousands or millions of lookups can become expensive because SQL Server repeatedly accesses the clustered index.

A suitable covering index may eliminate the lookup.

Interview Tip:
Do not automatically treat every Key Lookup as a problem. Its cost depends heavily on how many rows require the lookup.

59. Index Seek vs Index Scan?

Answer:
An Index Seek allows SQL Server to navigate an index structure to locate a specific row or range of rows.

An Index Scan reads a larger portion, and potentially all, of the relevant index.

Index Seek

Usually accesses a targeted subset of rows.
Often effective for selective predicates.
Can significantly reduce the number of pages read.

Index Scan

Reads a larger portion of an index.
May be appropriate when many rows are required.
Can be more efficient than repeated seeks for some workloads.

Common Interview Trap:
A Seek is not automatically good and a Scan is not automatically bad. The correct access method depends on selectivity, row counts, statistics, available indexes and query cost.

60. Can adding more indexes make SQL Server slower?

Answer:
Yes. Although additional indexes may improve SELECT performance, every additional index introduces maintenance overhead.

When data changes through:

INSERT
UPDATE
DELETE
MERGE

SQL Server may also need to modify affected indexes.

Too many indexes can therefore cause:

Slower INSERT, UPDATE and DELETE operations
Additional transaction log generation
Higher storage consumption
Longer backup times
Longer index maintenance windows
Additional memory pressure from index pages
More administrative complexity

DBA Principle:
The objective is not to create the maximum possible number of indexes. The objective is to maintain the smallest effective index set that supports the important workload.

61. What are included columns?

Answer:
Included columns are non-key columns stored at the leaf level of a nonclustered index.

They are commonly used to allow an index to cover a query without adding every required output column to the index key.

Example:

CREATE NONCLUSTERED INDEX IX_Orders_CustomerID
ON dbo.Orders(CustomerID)
INCLUDE
(
    OrderDate,
    OrderStatus,
    TotalAmount
);

In this example:

CustomerID is the index key column.
OrderDate is an included column.
OrderStatus is an included column.
TotalAmount is an included column.

Included columns can help eliminate Key Lookups for queries that require those values.

Interview Tip:
Keep columns needed for searching, joining or useful ordering in the index key when appropriate. Use INCLUDE primarily for additional columns needed to cover the query.

62. What are SQL Server statistics?

Answer:
Statistics contain information about the distribution of values in one or more columns.

The SQL Server Query Optimizer uses statistics to estimate how many rows are likely to qualify for an operation.

These estimates influence decisions such as:

Index Seek vs Scan
Join order
Nested Loops vs Hash Join vs Merge Join
Memory grant requirements
Parallel vs serial execution
Overall execution-plan choice

If statistics do not adequately represent the current data distribution, estimated row counts can differ significantly from actual row counts.

Statistics can be inspected using:

DBCC SHOW_STATISTICS
(
    'dbo.Orders',
    'IX_Orders_CustomerID'
);

Statistics can be manually updated when appropriate:

UPDATE STATISTICS dbo.Orders;

Or for a specific statistic or index:

UPDATE STATISTICS dbo.Orders
IX_Orders_CustomerID
WITH FULLSCAN;

Interview Tip:
Statistics do not store the complete contents of a column. They store statistical information that helps the Query Optimizer estimate data distribution and cardinality.

Useful DBA Script: Check Index Fragmentation

SELECT
    OBJECT_SCHEMA_NAME(ips.object_id) AS SchemaName,
    OBJECT_NAME(ips.object_id) AS TableName,
    i.name AS IndexName,
    ips.index_type_desc,
    ips.avg_fragmentation_in_percent,
    ips.avg_page_space_used_in_percent,
    ips.page_count
FROM sys.dm_db_index_physical_stats
(
    DB_ID(),
    NULL,
    NULL,
    NULL,
    'LIMITED'
) AS ips
INNER JOIN sys.indexes AS i
    ON ips.object_id = i.object_id
    AND ips.index_id = i.index_id
WHERE
    ips.index_id > 0
ORDER BY
    ips.avg_fragmentation_in_percent DESC;

Useful DBA Script: Check Index Usage

SELECT
    OBJECT_SCHEMA_NAME(i.object_id) AS SchemaName,
    OBJECT_NAME(i.object_id) AS TableName,
    i.name AS IndexName,
    ISNULL(us.user_seeks, 0) AS UserSeeks,
    ISNULL(us.user_scans, 0) AS UserScans,
    ISNULL(us.user_lookups, 0) AS UserLookups,
    ISNULL(us.user_updates, 0) AS UserUpdates
FROM sys.indexes AS i
LEFT JOIN sys.dm_db_index_usage_stats AS us
    ON i.object_id = us.object_id
    AND i.index_id = us.index_id
    AND us.database_id = DB_ID()
WHERE
    i.index_id > 0
ORDER BY
    ISNULL(us.user_updates, 0) DESC;

Important:
Do not drop an index simply because this DMV shows little or no usage. DMV statistics are reset by events such as SQL Server restarts, so usage should be evaluated over a representative workload period.

Useful DBA Script: Check Statistics Last Updated

SELECT
    OBJECT_SCHEMA_NAME(s.object_id) AS SchemaName,
    OBJECT_NAME(s.object_id) AS TableName,
    s.name AS StatisticsName,
    STATS_DATE(s.object_id, s.stats_id) AS StatisticsLastUpdated,
    sp.rows,
    sp.rows_sampled,
    sp.modification_counter
FROM sys.stats AS s
OUTER APPLY sys.dm_db_stats_properties
(
    s.object_id,
    s.stats_id
) AS sp
WHERE
    OBJECTPROPERTY(s.object_id, 'IsUserTable') = 1
ORDER BY
    StatisticsLastUpdated;

Simplified Index Tuning Flow


      Slow Query

         ↓

      Review Actual Execution Plan

         ↓

      Check Row Estimates and Statistics

         ↓

      Review Seeks, Scans and Key Lookups

         ↓

      Review Existing Indexes

         ↓

      Consider Key Columns and INCLUDE Columns

         ↓

      Test Proposed Index

         ↓

      Measure Reads, CPU and Duration

         ↓

      Evaluate DML and Storage Overhead

Interview Quick Recap

Indexes help SQL Server locate data efficiently.
A clustered index contains table rows at its leaf level.
Nonclustered indexes are separate structures containing keys and row locators.
Fragmentation alone should not determine the entire index maintenance strategy.
Reorganize and rebuild perform different types of index maintenance.
A covering index contains everything required to satisfy a query.
Key Lookups occur when additional columns must be retrieved from the clustered index after accessing a nonclustered index.
An Index Seek is not always good and an Index Scan is not always bad.
Excessive indexes can negatively affect write performance and increase storage and maintenance overhead.
Included columns can help cover queries without making every output column part of the index key.
Statistics help the Query Optimizer estimate row counts and choose an execution plan.

← Previous Topic
# Topic 5: Backup & Restore

Next Topic →

# Topic 7: Query Processing & Performance Tuning

Author: Ajit Kumar Patnaik
Technical Architects Zone (TAZ)
Practical Solutions | Real-World Experience | Continuous Learning

Search This Blog

Technical Architects Zone

SQL Server Q&A Topic 6: Indexes & Statistics