2

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 <> ?

0
12

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.