Does using inequality (<>) or NOT in where clause lead to SQL ignoring the index?

I found this article that talks about avoiding using <> operator in where clause because optimizer ignores the index.

https://www.mssqltips.com/sqlservertutorial/3203/avoid-using-not-equal-in-where-clause/

Is this absolutely true?

What is the best way to handle this? How to avoid using <> ?

A <> X predicate is sargable.

SQL Server can convert it to two range seeks (on X).

Unless the index is covering for the query however you may well not see this. The tipping point for a query using a non covering index and look ups is typically very low. (at most selectivity of a single digit percent).

So unless, say, 99% of the table does have value X the <> will match too many rows for that plan to be chosen.

Even in this case likely you should be considering a filtered index only containing the 1% of rows that don't match X rather than a full index including the highly unselective value.

Unless the domain of possible values is entirely fixed and quite small you don't really have a choice between using <> X or writing it as IN(all,values,that,are,not_x) - you need to choose the one with the correct semantics for all possible values.

StackExchange Database Administrators Q#273082, answer score: 12