Query Design Analysis - SQL Server Query Performance Tuning - page 365

To understand the performance benefit of the NOT NULL column constraint, consider the following example.
These two queries are intended to return every value that does not equal 'B' . These two queries are running against
similarly sized columns, each of which will require a table scan in order to return the data:
SELECT p.FirstName
FROM Person.Person AS p
WHERE p.FirstName < 'B'
OR p.FirstName >= 'C';
SELECT p.MiddleName
FROM Person.Person AS p
WHERE p.MiddleName < 'B'
OR p.MiddleName >= 'C';
The two queries use identical execution plans, as you can see in Figure 18-16 .
Figure 18-16. Table scans caused by a lack of indexes
Since the column Person.MiddleName can contain NULL , the data returned is incomplete. This is because, by
definition, although a NULL value meets the necessary criteria of not being in any way equal to 'B' , you can't return
NULL values in this manner. An added OR clause is necessary. That would mean modifying the second query like this:
SELECT p.FirstName
FROM Person.Person AS p
WHERE p.FirstName < 'B'
OR p.FirstName >= 'C' ;
SELECT p.MiddleName
FROM Person.Person AS p
WHERE p.MiddleName < 'B'
OR p.MiddleName >= 'C'
OR p.MiddleName IS NULL;