Write Sargable SQL Server Predicates

An index cannot help when a predicate hides its column inside a function or calculation. SQL Server may scan every candidate row instead of navigating to a range.

What to measure

Inspect seek and residual predicates, logical reads, estimates, and expressions applied to indexed columns. Date functions, leading wildcards, arithmetic, and null-handling functions are common causes.

Practical approach

Express dates as half-open ranges, move calculations to constants or parameters, consider persisted computed columns for stable business expressions, and align composite indexes with equality and range predicates.

What to avoid

Do not add another index before rewriting the predicate, use BETWEEN carelessly with datetime values, or assume every seek is efficient.

Operational result

Sargability gives the optimizer a searchable range and often reduces work more effectively than a hint.

Production checklist

Capture a baseline and define the expected improvement. Test with representative data and concurrency. Preserve the original setting or plan, prepare a rollback, deploy during an appropriate window, and monitor the next normal workload peak.

If you want help applying this method to a specific SQL Server environment, use the question form below and include the SQL Server version, database size, workload pattern, and evidence already collected.