SARGability and implicit conversion

Write predicates an index can use.

UnderstandStudy an examplePractise

Understand the concept

A SARGable predicate allows an access path to use search boundaries. A date range is generally a better candidate than YEAR(OrderDate) = 2026. It does not guarantee a seek: statistics, selectivity and cost still matter.

Functions or type conversion on an indexed column can reduce access options. Parameter/column mismatches can introduce implicit conversion; which side converts matters. A prefix LIKE pattern can sometimes become a range, while a leading wildcard generally cannot provide the same B-tree advantage. Do not break collation, language or business semantics just to alter a plan.

WHEN TO USE IT

Make filtering both correct and measurable.

COMMON PITFALL

Promising a speedup merely because a function was removed.

01 / PRACTICE

One topic. Three levels of depth.

T-SQL / SQL Server 2019+

SELECT OrderId FROM lab.SalesOrder WHERE OrderDate >= '20260101' AND OrderDate < '20270101';

Bounds the year without transforming the column.

This page does not execute SQL. Practise in the downloadable SqlStudioLab using SSMS. Before rerunning temporary-table examples, remove only the relevant #table. Views, procedures and functions create persistent lab objects; never use a production connection.

02 / YOUR TURN

Try it yourself first.

Filter February orders without YEAR or MONTH.

Show the solution and explanation

Worked solution

SELECT OrderId FROM lab.SalesOrder WHERE OrderDate >= '20260201' AND OrderDate < '20260301';

Returns 103, 104 and 105; March 1 is excluded.

RasimAtaberkŞahin

Understand the concept

One topic. Three levels of depth.

Try it yourself first.