Managing Indexes

Overview ▾

Indexes improve query performance by allowing faster data retrieval. Proper management ensures they remain efficient as data changes.

Creating new indexes
Modifying existing indexes
Rebuilding and reorganizing
Dropping unused indexes

Creating Indexes ▾

Use CREATE INDEX or CREATE UNIQUE INDEX for non‑clustered indexes and CREATE CLUSTERED INDEX for clustered indexes.

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

For full‑text search, employ CREATE FULLTEXT INDEX.

Altering Indexes ▾

SQL Server does not support direct alteration of an index definition. Instead, drop and recreate the index with the new definition.

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

Rebuilding & Reorganizing ▾

Fragmentation reduces performance. Use ALTER INDEX … REBUILD for heavy fragmentation and ALTER INDEX … REORGANIZE for light fragmentation.

-- Rebuild all indexes on a table
ALTER INDEX ALL ON dbo.Orders REBUILD WITH (FILLFACTOR = 80);

-- Reorganize a specific index
ALTER INDEX IX_Orders_CustomerID ON dbo.Orders REORGANIZE;

Dropping Indexes ▾

Remove unused indexes to improve write performance and reduce storage.

DROP INDEX IF EXISTS IX_Orders_CustomerID ON dbo.Orders;

Monitoring Index Health ▾

Use dynamic management views to assess fragmentation and usage.

SELECT 
    i.name AS IndexName,
    s.avg_fragmentation_in_percent,
    s.page_count
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') AS s
JOIN sys.indexes AS i
    ON i.object_id = s.object_id AND i.index_id = s.index_id
WHERE s.database_id = DB_ID()
  AND s.page_count > 100
ORDER BY s.avg_fragmentation_in_percent DESC;

Best Practices ▾

Index columns used in WHERE, JOIN, and ORDER BY clauses.
Avoid over‑indexing; each index adds overhead on DML operations.
Prefer covering indexes with INCLUDE for frequently selected columns.
Regularly review fragmented indexes and usage statistics.
Use appropriate fill factor based on insert/update patterns.