query-optimization

# Query Optimization

Comprehensive guide to T-SQL query optimization techniques.

## Quick Reference

### SARGable vs Non-SARGable Patterns

| Non-SARGable (Bad) | SARGable (Good) |
|-------------------|-----------------|
| `WHERE YEAR(Date) = 2024` | `WHERE Date >= '2024-01-01' AND Date < '2025-01-01'` |
| `WHERE LEFT(Name, 3) = 'ABC'` | `WHERE Name LIKE 'ABC%'` |
| `WHERE Amount * 1.1 > 1000` | `WHERE Amount > 1000 / 1.1` |
| `WHERE ISNULL(Col, 0) = 5` | `WHERE Col = 5 OR Col IS NULL` |
| `WHERE VarcharCol = 123` | `WHERE VarcharCol = '123'` |

### Join Types Performance

| Join Type | Best For | Characteristics |
|-----------|----------|-----------------|
| Nested Loop | Small outer, indexed inner | Low memory, good for small sets |
| Merge Join | Sorted inputs, similar sizes | Efficient for sorted data |
| Hash Join | Large unsorted inputs | High memory, good for large sets |

### Query Hints Quick Reference

| Hint | Purpose |
|------|---------|
| `OPTION (RECOMPILE)` | Fresh plan each execution |
| `OPTION (OPTIMIZE FOR (@p = value))` | Optimize for specific value |
| `OPTION (OPTIMIZE FOR UNKNOWN)` | Use average statistics |
| `OPTION (MAXDOP n)` | Limit parallelism |
| `OPTION (FORCE ORDER)` | Use exact join order |
| `WITH (NOLOCK)` | Read uncommitted (dirty reads) |
| `WITH (FORCESEEK)` | Force index seek |

## Core Optimization Principles

### 1. SARGability

SARG = Search ARGument. SARGable queries can use index seeks:

```sql
-- Non-SARGable: Function on column
WHERE DATEPART(year, OrderDate) = 2024
WHERE UPPER(CustomerName) = 'JOHN'
WHERE OrderAmount + 100 > 500

-- SARGable: Preserve column
WHERE OrderDate >= '2024-01-01' AND OrderDate < '2025-01-01'
WHERE CustomerName = 'john' COLLATE SQL_Latin1_General_CP1_CI_AS
WHERE OrderAmount > 400
```

### 2. Implicit Conversions

Avoid data type mismatches:

```sql
-- Bad: Implicit conversion (varchar column compared to int)
WHERE VarcharColumn = 12345

-- Good: Match types exactly
WHERE VarcharColumn = '1234