SQL Database – Index Design

Effective indexing is a cornerstone of high‑performance Azure SQL Database solutions. This guide walks you through index types, design best practices, and tools to help you tune and maintain your indexes.

Understanding Indexes

An index is a data structure that improves the speed of data retrieval operations on a database table at the cost of additional writes and storage space.

CREATE INDEX IX_Employee_LastName
ON dbo.Employee (LastName);

When deciding whether to create an index, consider query patterns, selectivity, and the impact on DML operations.

Common Index Types

Type	Description	Use Cases
Clustered	Stores rows in the leaf level of the index.	Primary keys, high‑cardinality columns.
Non‑Clustered	Separate structure with pointers to data rows.	Covering queries, frequent filters.
Columnstore	Column‑oriented storage for analytical workloads.	Data warehousing, large scans.
Filtered	Indexes a subset of rows.	Sparse data, selective queries.
XML	Indexes XML data.	XML query optimization.

Best Practices

Start with a clustered primary key on a narrow, unique column.
Use non‑clustered covering indexes to include all columns used by a query.
Employ filtered indexes for infrequent values.
Regularly monitor index fragmentation and rebuild/reorganize as needed.
Leverage the Query Store to identify missing indexes.

Example of a covering index:

CREATE NONCLUSTERED INDEX IX_Order_Covering
ON dbo.Orders (CustomerId, OrderDate)
INCLUDE (TotalAmount, Status);

Tools & Scripts

Azure SQL provides several built‑in tools to help you manage indexes:

Automatic Index Tuning

Dynamic Management Views (DMVs):

SELECT * FROM sys.dm_db_index_usage_stats
WHERE database_id = DB_ID();

Maintenance Plans for automated rebuilds.