SARGability means writing predicates SQL Server can search efficiently.
Complete 12 of 24 practices (50%) and enter your name to unlock the Certificate of Participation.
A SARGable predicate is written so SQL Server can use an index key to narrow the search efficiently. The core principle is simple: keep the indexed column exposed in a searchable comparison whenever possible.
If SQL Server must calculate YEAR(OrderDate) for every row before deciding whether it matches, the engine may lose the direct ability to navigate the OrderDate index by range.
-- Less SARGable
WHERE YEAR(OrderDate) = 2026
-- More SARGable
WHERE OrderDate >= '2026-01-01'
AND OrderDate < '2027-01-01';SARGability is about preserving the engine's ability to navigate the index key directly.
Functions can make SQL expressive, but wrapping an indexed column in a function often hides the raw key values from the access method. The usual strategy is to rewrite the predicate around the stored value.
Instead of transforming every LastName with LEFT(), describe the range of names that logically match the same condition.
-- Less SARGable
WHERE LEFT(LastName, 1) = 'S'
-- More SARGable range
WHERE LastName >= 'S'
AND LastName < 'T';The safest rewrite preserves business meaning while exposing the indexed value directly.
Even a simple equality predicate can become non-SARGable when the parameter and indexed column use incompatible data types. SQL Server follows type-precedence rules and may convert the column side.
Comparing an nvarchar parameter to a varchar indexed column can introduce a conversion warning or conversion operator that changes the access path.
-- Indexed column:
CustomerCode varchar(20)
-- Better parameter:
DECLARE @CustomerCode varchar(20);
SELECT ...
FROM dbo.Customer
WHERE CustomerCode = @CustomerCode;
-- Avoid forcing:
WHERE CONVERT(nvarchar(20), CustomerCode) = @p;Type consistency is part of query performance design, not only data modeling.
Dates are one of the most common places where SARGability is lost. The safest pattern for periods is usually a direct half-open range: greater than or equal to the start, less than the next boundary.
Instead of converting every datetime to date, define the exact interval that contains the day, month or year you want.
DECLARE @Day date = '2026-09-24';
SELECT ...
FROM dbo.Events
WHERE EventDate >= @Day
AND EventDate < DATEADD(day, 1, @Day);For temporal data, a well-formed range is often both clearer and more optimizer-friendly than extracting date parts.
Convenient NULL-handling expressions can hide indexed values behind functions. The challenge is to preserve correct three-valued logic while keeping the column directly searchable where practical.
Do not rewrite NULL handling into something faster but semantically wrong. The best predicate keeps the business rule explicit and gives the optimizer useful access paths.
WHERE Status = @Status
OR (Status IS NULL AND @Status IS NULL)
-- Possible filtered index example:
CREATE INDEX IX_Order_Open
ON dbo.Orders(OrderDate)
WHERE ClosedDate IS NULL;SARGability never justifies changing the meaning of NULL logic; correctness remains the first constraint.
B-tree indexes are strongest when SQL Server knows the beginning of the search range. A trailing wildcard preserves that starting point; a leading wildcard usually removes it.
LIKE 'Juan%' can navigate to the J-u-a-n range. LIKE '%Juan%' asks SQL Server to find the text anywhere inside each value, which often requires much broader work.
-- More index-friendly:
WHERE LastName LIKE 'Smith%'
-- Usually broad scan-style work:
WHERE LastName LIKE '%Smith%'
-- If substring search is core:
-- evaluate Full-Text Search or
-- another search-oriented design.Do not force a B-tree index to solve a search problem it was not designed to solve.
Sometimes you cannot remove the transformation because users truly search by that transformed value. In those cases, a computed column can make the transformed value part of the schema and potentially index it.
If every query searches a normalized phone number or date bucket, repeatedly computing it in WHERE may be a signal that the transformed value deserves a modeled, indexable representation.
ALTER TABLE dbo.Customer
ADD NormalizedPhone AS
(
REPLACE(REPLACE(REPLACE(Phone,'-',''),'(',''),')','')
) PERSISTED;
CREATE INDEX IX_Customer_NormalizedPhone
ON dbo.Customer(NormalizedPhone);
SELECT ...
FROM dbo.Customer
WHERE NormalizedPhone = @NormalizedPhone;Computed columns are a schema-level answer when a transformation is unavoidable and repeatedly searched.
SARGability is a tuning principle, not a religion. A seek is not automatically better than a scan, and some transformations are necessary. The goal is to remove avoidable barriers between the predicate and the index key, then prove the benefit.
If a non-SARGable predicate scans 20 rows, there may be nothing worth fixing. If it scans 200 million rows to return 12, the rewrite may be transformational.
1. Which predicate is expensive?
2. Is an indexed column wrapped in a function?
3. Is there an implicit conversion?
4. Can a date-part test become a range?
5. Is a leading wildcard unavoidable?
6. Is NULL logic explicit and correct?
7. Would a computed column model a required transformation?
8. Are logical reads lower after the rewrite?
9. Is the new result set exactly equivalent?
10. Does the actual plan improve under representative data?The strongest SARGable query is the one that remains correct and gives SQL Server a better access path under real workload conditions.
Open each item after answering it in your own words. The 24 interactive practices above drive certificate progress.
A predicate written so SQL Server can search indexed key values efficiently.
The function transforms the indexed column; a direct range exposes searchable boundaries.
SQL Server may convert the indexed column side and reduce seek opportunities.
The known prefix gives SQL Server a starting key range.
Confirm identical results and compare actual runtime evidence.
| Less searchable pattern | Better direction |
|---|---|
| YEAR(DateCol) = 2026 | DateCol >= start AND DateCol < next_start |
| LEFT(Name,1) = 'S' | Name >= 'S' AND Name < 'T' |
| CONVERT(..., IndexedCol) = @p | Match parameter type to IndexedCol |
| LIKE '%term%' | Use prefix search or search-specific technology |
| ISNULL(Col,'') = @p | Use explicit NULL logic when semantically equivalent |
Complete at least 12 of the 24 practice cases (50%) and enter your name.
SARGability is about removing avoidable barriers between predicates and index keys.