SQL Server Interview Question #121

What is an index in SQL Server?

Indexes & Query Performance Senior Advanced

Detailed Explanation

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.

Code Example

CREATE INDEX IX_Products_CategoryId
ON dbo.Products(CategoryId);