Adding indexes on a specific table doesn't guarantee you the queries will use these indexes effectively. These are some of the query design rules you should follow to improve the use of indexes:
Avoid nonsargable search conditions
A sargable predicate in a query is one in which an index can be used.
Sargable | =, >, >=, <, <= BETWEEN LIKE 'text%' |
---|---|
Nonsargable | <>, != NOT EXISTS NOT IN OR LIKE '%text' |
Avoid arithmetic operators on the WHERE clause column
For example, assuming an index has been created on NoWeeks column, the use of the multiplication operator column in the first query prevents the optimizer from using (choosing) the index on the column:
Avoid functions on the WHERE clause column