Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
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 or > 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.
