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.
The following predicates CAN be matched to an index seek operation:
=,>,>=,<,<=, and BETWEEN, and some LIKE conditions such as LIKE 'Search%'
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 ID BETWEEN 51825 AND 51828 WHERE Name LIKE 'Ice%' WHERE ProductID = 771 WHERE UnitPrice < 3.975
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'
WHERE MONTH(Startdate)=5 AND year(Startdate)=2016
You might want to see more details about it in the links below.