sql server 2005 indexes and low cardinality
- by Peanut
How does SQL Server determine whether a table column has low cardinality?
The reason I ask is because query optimizer would most probably not use an index on a gender column (values 'm' and 'f'). However how would it determine the cardinality of the gender column to come to that decision?
On top of this, if in the unlikely event that I had a million entries in my table and only one entry in the gender column was 'm', would SQL server be able to determine this and use the index to retrieve that single row? Or would it just know there are only 2 distinct values in the column and not use the index?
I appreciate the above discusses some poor db design, but I'm just trying to understand how query optimizer comes to its decisions.
Many thanks.
