14. Clustered, non-clustered, covering and filtered indexes

• Clustered index — defines the physical order of rows (the table is the B-tree leaf level). One per table. Default on the PK. Best on narrow, unique, ever-increasing keys (INT IDENTITY).
• Non-clustered index — separate B-tree with key columns + a row locator (clustered key, or RID for heaps). Up to 999 per table.
• Covering index — contains all columns a query needs (keys + INCLUDE columns) → no key lookups.
• Filtered index — index on a subset (WHERE IsActive = 1).
• Unique index, composite index (column order matters: equality columns first, then range/sort), columnstore (analytics, compression), full-text, XML/spatial.
• Heap — table without a clustered index.

CREATE NONCLUSTERED INDEX IX_Orders_Customer_Date
ON dbo.Orders (CustomerId, OrderDate DESC)
INCLUDE (Total, Status)
WHERE IsDeleted = 0;

CREATE CLUSTERED COLUMNSTORE INDEX CCI_FactSales ON dbo.FactSales;

Costs: indexes speed reads but slow INSERT/UPDATE/DELETE and use storage. Find missing/unused ones with sys.dm_db_missing_index_details and sys.dm_db_index_usage_stats (validate suggestions — don't blindly create).