Inequality logic is logic that involves negative comparisons, such as !=, <>, NOT IN, or NOT LIKE. This type of predicate can be costly because it often results in evaluating each row, which translates to scan operations. Consider the following queries from the AdventureWorks database:
SELECT BusinessEntityID, FirstName, LastName
FROM Person.Person
WHERE PersonType NOT IN ('EM','SP','IN','VC','GC');
SELECT BusinessEntityID, FirstName, LastName
FROM Person.Person
WHERE PersonType = 'SC';
These queries are logically equivalent, since SC is the only PersonType that is not listed in the first query. Out of the box, the execution plans look like this:
At this point, they appear to have the same estimated cost, but notice that both are doing a Clustered Index Scan and there is a missing index suggestion from...