[dba-SQLServer] Indexing on a small number of values in a column

On 14 Dec 2006 at 21:19, artful at rogers.com wrote:

> I have heard or read that creating an index on a column containing a small
> number of values is pointless.
> 
> a) Given a column Sex with a constraint "M/F", is there any point indexing
> this column? b) Given the same column defined as a bit, you can't index it
> (afaik). c) Assuming that it is pointless to index a column containng < n
> distinct values, what is the value of n? Assume 100M rows in the table of
> interest?

See:
http://msdn2.microsoft.com/en-us/library/aa224773(sql.80).aspx
"My tests show that a table scan often starts to perform better than a 
nonclustered index access when at least 10 percent of the rows are 
selected. I also found that the optimizer switches from nonclustered index 
access to table scan prematurely (i.e., when nonclustered index access 
still shows better response time than the corresponding table scan). In 
many cases, the optimizer forces a table scan for queries with result sets 
of approximately 5 percent, although the table scan becomes more efficient 
than index access at selectivities of 8 to 10 percent."

also
http://www.sql-server-performance.com/am_indexing_low_sel_cols.asp
for some real life tests and ideas on how to measure the effectiveness of 
an index.


-- 
Stuart