When indexes are used:
when u equate a value of a range of values for an indexed column.
using functions, concatenating something on cols restrict the use of indexes.
using max and min on an index col uses and index.
In most cases no:
is not null will not, but will if the selected col is part of a index.
!= and not in will not, but will if the selected col is part of a index and if the optimizer fells that it would be faster.
When indexes are not used:
is null will not use an index since nulls are not stored in index.