Indexing in Azure SQL Database

Indexes are critical for query performance in Azure SQL Database. This guide explains how to create, manage, and monitor indexes to ensure optimal data retrieval.

Why Indexes Matter

Reduce I/O by allowing the engine to locate rows quickly.
Support efficient sorting and grouping operations.
Enable the optimizer to choose better execution plans.

Types of Indexes

Index Type	Description
Clustered	Defines the physical order of rows in the table.
Non‑clustered	Separate structure that maps keys to row locations.
Columnstore	Optimized for analytical workloads and large scans.
Filtered	Indexes a subset of rows using a WHERE clause.

Creating an Index

Use the CREATE INDEX statement. Below is an example of a non‑clustered index on Orders table.

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

Index Maintenance

Regularly rebuild or reorganize indexes to reduce fragmentation.

-- Rebuild fragmented indexes
ALTER INDEX ALL ON dbo.Orders
REBUILD WITH (FILLFACTOR = 80, ONLINE = ON);
    
-- Reorganize low‑fragmentation indexes
ALTER INDEX ALL ON dbo.Orders
REORGANIZE;

Monitoring Index Usage

Query sys.dm_db_index_usage_stats to see how often indexes are used.

SELECT OBJECT_NAME(i.object_id) AS TableName,
       i.name AS IndexName,
       s.user_seeks, s.user_scans,
       s.user_lookups, s.user_updates
FROM sys.indexes AS i
JOIN sys.dm_db_index_usage_stats AS s
  ON i.object_id = s.object_id
 AND i.index_id = s.index_id
WHERE OBJECTPROPERTY(i.object_id,'IsUserTable') = 1
  AND i.type_desc <> 'HEAP'
ORDER BY s.user_seeks DESC;

Best Practices

Index columns used in WHERE, JOIN, ORDER BY, and GROUP BY clauses.
Avoid over‑indexing; each index adds write overhead.
Consider filtered indexes for sparse data.
Use columnstore indexes for large fact tables.

For deeper insights, explore the Query Tuning guide.