If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

Unanswered: Guideline with this index

Kindly help in clearing the concepts:

I've a table with more than 200 thousands records. there's a column named CLAIM(Primary key + clustered index).
When I run DBCC SHOW_STATISTICS for the non-clustered index
I get ALL DENSITY 8.009868E-6.
If Density refers to the average percentate of duplicate rows in and index, how come the value is 8. There isn't any duplicate record for the CLAIM key.
The statistics for the index is current. What does the high density value for the key depicts?

Because it does an implicity datatype conversion, and at that point it chooses not to use the index. In 6.5 it was guaranteed to be the case, in 2K "sometimes" the optimizer uses the ind3e3x even if the conversion is required.

"The data in a record depends on the Key to the record, the Whole Key, and
nothing but the Key, so help me Codd."

I love nothing more than to followin' you around and pickin' up your scraps

selectivity would more likely refer to the amount of rows returned in a query based on the total number of rows in the table.

since this question had nothing to do directly with a query, i suggest that
density would be the more correct term as to defining the amount of duplicates in a column.
ex Low density = mostly unique
High density = mostly duplicated.