Sargable queries – Using indexes effectively

It’s really important to have effective indexes in your database, but also equally important to ensure that yours queries are written properly to use these indexes effectively.
So, one thing that we always need to keep in mind is to avoid using Nonsargable search conditions.

The word sargable is a contraction of “Search ARGument ABLE”, where the optimizer has the ability to seek a row in the index.

Sargable Conditions
The following predicates CAN be matched to an index seek operation:

=,>,>=,<,<=, and BETWEEN, and some LIKE conditions such as LIKE 'Search%'

Nonsargable Conditions
An index cannot be used to seek in the following predicates:

<>,!=,!>,!<, NOT EXISTS, NOT IN, and N0T LIKE IN, OR, and some
LIKE conditions such as LIKE '%Search'

So, basically you should use this:

WHERE Name LIKE 'Ice%'
WHERE ProductID = 771
WHERE UnitPrice < 3.975
rather than this:
WHERE ID IN (51825, 51826, 51827, 51828)
WHERE Name LIKE '%Ice%'
WHERE LEFT(Name,3) ='Ice'
WHERE ABS(ProductID) = 771
WHERE ProductID <> 771
WHERE ProductID NOT IN (771,772)
WHERE UnitPrice + 1 < 3.975

Also, we shouldn’t use functions in the left side (the field side). Functions on WHERE clause columns also hurt query performance for the same reasons as mentioned before.

So, we should use this

WHERE Startdate >= '20160501' and Startdate < '20160601'

rather than

WHERE MONTH(Startdate)=5 AND year(Startdate)=2016

You might want to see more details about it in the links below.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s