Inequality logic

Inequality logic is logic that involves negative comparisons such as !=, <>, NOT IN, and 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, 1 and 2, 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:

Figure 5.38 – Execution plan for Query 1 and Query 2

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