SQL Server Interview Question #129

What is a covering index?

Indexes & Query Performance Senior Advanced

Quick Interview Answer

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.

Detailed Explanation

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.

Code Example

CREATE INDEX IX_Products_Category_Covering
ON dbo.Products(CategoryId)
INCLUDE (Name, Price, IsActive);

SELECT Name, Price, IsActive
FROM dbo.Products
WHERE CategoryId = 30;