Query Performance with NULL

Question

I would like to know about how NULL values affect query performance in SQL Server 2005.

I have a table similar to this (simplified):

ID | ImportantData | QuickPickOrder
--------------------------
1  | 'Some Text'   | NULL
2  | 'Other Text'  | 3
3  | 'abcdefg'     | NULL
4  | 'whatever'    | 4
5  | 'it is'       | 2
6  | 'technically' | NULL
7  | 'a varchar'   | NULL
8  | 'of course'   | 1
9  | 'but that'    | NULL
10 | 'is not'      | NULL
11 | 'important'   | 5

And I'm doing a query on it like this:

SELECT   *
FROM     MyTable
WHERE    QuickPickOrder IS NOT NULL
ORDER BY QuickPickOrder

So the QuickPickOrder is basically a column used to single out some commonly chosen items from a larger list. It also provides the order in which they will appear to the user. NULL values mean that it doesn't show up in the quick pick list.

I've always been told that NULL values in a database are somehow evil, at least from a normalization perspective, but is it an acceptable way to filter out unwanted rows in a WHERE constraint?

Would it be better to use specific number value, like -1 or 0, to indicate items that aren't wanted? Are there other alternatives?

EDIT: The example does not accuratly represent the ratio of real values to NULLs. An better example might show at least 10 NULLs for every non-NULL. The table size might be 100 to 200 rows. It is a reference table so updates are rare.

Answer 1

SQL Server indexes NULL values, so this will most probably just use the Index Seek over an index on QuickPickOrder, both for filtering and for ordering.