Azure SQL Database – Indexes

On this page Overview Types of Indexes Create an Index Index Maintenance Best Practices Performance Tips Resources

Overview

Indexes in Azure SQL Database improve query performance by providing quick lookup paths to rows in a table. Azure SQL Database supports the same index types as on-premises SQL Server, with cloud‑specific considerations for scalability, cost, and automated maintenance.

A well‑designed index strategy can reduce latency, lower DTU/DTU‑based cost, and help meet SLA requirements.

Types of Indexes

Index Type	Purpose	Supported in Azure SQL DB
Clustered	Defines the physical order of rows	✓
Non‑clustered	Secondary lookup structure	✓
Columnstore	Analytics‑heavy workloads	✓
Filtered	Indexes a subset of rows	✓
Spatial	Optimizes spatial data queries	✓
XML	Indexes XML columns	✓
Full‑Text	Searches textual content	✓ (requires full‑text service)

Create an Index

Use CREATE INDEX or CREATE CLUSTERED INDEX. Below is an example of creating a filtered non‑clustered index.

-- Example: Filtered non‑clustered index on Orders table
CREATE NONCLUSTERED INDEX IX_Orders_CustomerId_Status
ON dbo.Orders (CustomerId)
WHERE OrderStatus = 'Completed';

To create a columnstore index for a large fact table:

CREATE CLUSTERED COLUMNSTORE INDEX CX_Orders_Fact
ON dbo.OrdersFact;

Index Maintenance

Azure SQL Database offers automatic index management with Automatic Tuning. You can also schedule manual maintenance.

Enable CREATE INDEX and DROP INDEX recommendations via the portal or PowerShell.
Use ALTER INDEX … REBUILD for fragmented indexes.
Consider ONLINE = ON for minimal downtime.

ALTER INDEX IX_Orders_CustomerId_Status
ON dbo.Orders
REBUILD WITH (ONLINE = ON);

Best Practices

Start with a clustered index on a primary key or a natural surrogate.
Limit the number of non‑clustered indexes per table (5–10 is a typical guideline).
Use filtered indexes to target high‑selectivity queries.
Prefer columnstore indexes for analytical workloads.
Monitor index usage via sys.dm_db_index_usage_stats.

Performance Tips

Combine indexing with query tuning for best results.

-- Identify missing indexes
SELECT *
FROM sys.dm_db_missing_index_details
WHERE database_id = DB_ID();

-- Check index fragmentation
SELECT object_name(i.object_id) AS TableName,
       i.name AS IndexName,
       avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, 'LIMITED') AS ps
JOIN sys.indexes AS i
  ON ps.object_id = i.object_id
 AND ps.index_id = i.index_id
WHERE avg_fragmentation_in_percent > 30;