title: Query Performance with NULL
body: <p>I would like to know about how NULL values affect query performance in SQL Server 2005.</p>
  
  <p>I have a table similar to this (simplified):</p>
  
  <pre><code>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
  </code></pre>
  
  <p>And I'm doing a query on it like this:</p>
  
  <pre><code>SELECT   *
  FROM     MyTable
  WHERE    QuickPickOrder IS NOT NULL
  ORDER BY QuickPickOrder
  </code></pre>
  
  <p>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.</p>
  
  <p>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?</p>
  
  <p>Would it be better to use specific number value, like -1 or 0, to indicate items that aren't wanted? Are there other alternatives?</p>
  
  <p><strong>EDIT:</strong>
  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.</p>