query-optimization

| 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' |

| 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 |

| 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 |