SQL Server 2008 multi columns statistics not being used by row count estimation

Question

i have a table with columns A and B which are correlated in the following meaning:

A == 1 => B == 3
A == 2 => B == 0
A == 3 => B == 7

with => meaning implies with high probability. I want to query like this:

where A = 2 and B = 3 --very few rows should match

so i have created statistics on (A, B). however the query optimizer completely misestimates the rowcount by a factor of 10. any ideas why? the joint histogram should allow for a very precise estimate. btw: cardinality(A) = 10, cardinality(B) = 5

Answer 1

I suspect the optimiser is ignoring the statistics because the whole query matters, not just the WHERE clause. Or you have SELECT *. Or the sample rate is too low (did you use FULLSCAN?)

What if you create an index, which has index statistics? With inclusions too to make it covering?

I've never created my own column statistics (unless a graphical query plan has mentioned it). If I need to create them, an index is highly likely to serve my purposes better

Edit:

• Another SO question:
• SQLServerPedia article