index-strategies

# Index Strategies

Comprehensive guide to SQL Server index design and optimization.

## Quick Reference

### Index Types

| Type | Description | Best For |
|------|-------------|----------|
| Clustered | Table data order | Primary access path, range scans |
| Nonclustered | Separate structure | Specific query patterns |
| Columnstore | Column-based storage | Analytics, aggregations |
| Filtered | Partial index | Well-known subsets |
| Covering | All columns needed | Avoiding key lookups |

### Clustered Index Guidelines

**Ideal Clustered Key:**
- Narrow (small data type)
- Unique or mostly unique
- Ever-increasing (identity, sequential GUID)
- Static (rarely updated)

```sql
-- Good: Identity column
CREATE CLUSTERED INDEX CIX_Orders ON Orders(OrderID);

-- Good: Sequential GUID
CREATE TABLE Orders (
    OrderID UNIQUEIDENTIFIER DEFAULT NEWSEQUENTIALID() PRIMARY KEY CLUSTERED
);

-- Avoid: Wide composite keys, frequently updated columns, GUIDs (NEWID)
```

### Nonclustered Index Design

```sql
-- Basic index
CREATE NONCLUSTERED INDEX IX_Orders_CustomerID
ON Orders(CustomerID);

-- Covering index (avoids key lookup)
CREATE NONCLUSTERED INDEX IX_Orders_CustomerID_Cover
ON Orders(CustomerID)
INCLUDE (OrderDate, TotalAmount, Status);

-- Filtered index (partial)
CREATE NONCLUSTERED INDEX IX_Orders_Active
ON Orders(CustomerID, OrderDate)
WHERE Status = 'Active';

-- Descending order
CREATE NONCLUSTERED INDEX IX_Orders_DateDesc
ON Orders(OrderDate DESC, OrderID DESC);
```

## Index Selection Guide

### By Query Pattern

| Pattern | Recommended Index |
|---------|-------------------|
| `WHERE Col = value` | Nonclustered on Col |
| `WHERE Col = v1 AND Col2 = v2` | Nonclustered on (Col, Col2) |
| `WHERE Col = v ORDER BY Col2` | Nonclustered on (Col, Col2) |
| `WHERE Col BETWEEN x AND y` | Col as leftmost key |
| `SELECT * WHERE Col = v` | Clustered or covering NC |
| Large aggregations | Columnstore |
| Specific subset queries | Filtered index |

### Column Order in Composi