Does using inequality () or NOT in where clause lead to SQL ignoring the index? I found this article that talks about avoiding using &lt;&gt; 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 &lt;&gt; ?
A &lt;&gt; X predicate is sargable.
SQL Server can convert it to two range seeks (on &lt; X or &gt; 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 &lt;&gt; 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 &lt;&gt; 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.