SARGable Predicates for Index Optimization

Snippet

SARGable Predicates for Index Optimization

Expert performance tuning requires 'Search Argumentable' (SARGable) queries. Instead of applying functions like YEAR(order_date), which prevent index usage, we use a range comparison. This allows the database engine to perform an index seek rather than a full table scan.

snippet.sql
sql
SELECT order_id, customer_id
FROM orders
WHERE order_date >= '2023-01-01' 
  AND order_date < '2024-01-01'
  AND status = 'COMPLETED';

Breakdown

WHERE order_date >= '2023-01-01'

Provides a lower bound that the query optimizer can use for index traversal.

AND order_date < '2024-01-01'

Provides an upper bound without wrapping the column in a function, maintaining SARGability.

Previous snippet Next snippet

More Expert SQL exercises →

From your library

SARGable Predicates for Index Optimization

Related