Accurate stats yet the histogram doesn't have that value
SELECT * FROM table WHERE date IS NULL;
This is the first 4 rows from the histogram of that index. It doesn't have a NULL in the histogram. However, the plan shows that estimated rows and actual rows are the same: 18206. How did the optimizer figure that out? I am going to answer my own question and look for confirmation or rebuke.
The total number of rows in the table is 504974 (The header of stats is below). The sum of all key values from the histogram is 486591 there are two steps with average rows of 53 and 124. If you perform this: 504974 – 486591 – 53 – 126 you get 18206. Seems a coincidence for that to be not how the optimizer got that answer. How does it know though that it means the cardinality of the NULL values?
SELECT * FROM table WHERE date = '2009-09-05'
That value isn't in the histogram and is before the first step yet the optmizer recognizes that there are not any rows with that value and produces an esitmate of zero.
Name Updated Rows Rows Sampled Steps Density Average key length String Index date Sep 11 2014 12:47PM 504974 504974 198 0.01129944 19.71157 NO
RANGE_HI_KEY RANGE_ROWS EQ_ROWS DISTINCT_RANGE_ROWS AVG_RANGE_ROWS 2009-09-06 00:00:00.000 0 109 0 1 2009-09-16 00:00:00.000 0 746 0 1 2009-09-26 00:00:00.000 0 304 0 1 2009-10-06 00:00:00.000 0 674 0 1