An index operation on the columns(s) in WHERE clause, can be performed or not, depends upon your search conditions. Rule is simple. Exclusion searches generally prevent the optimizer from using an index on the columns, referred to in the WHERE clause. While Inclusion search conditions are also helpful for SQL Server to perform index seek operations. Though every time it is not possible, still when ever it is possible, try to avoid using exclusion search conditions. Here is a list of both exclusion and inclusion search conditions.
Read more: Connect SQL
Read more: Connect SQL