Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I've read in several articles that the density of a column is (1.0 / count(distinct columnname)). I have a column with roughly 2.6 million distinct values over 2.7 million rows, but the column density in DBCC show_statistics even AFTER updating statistics with fullscan is .48.

I am confused as to where this number could be coming from. Can anyone explain it to me?

share|improve this question
1  
Are you talking about the density in the STAT_HEADER? BOL Says this isn't used anyway –  Martin Smith Oct 29 '13 at 18:25
1  
BOL says it's not used for 2008 and higher. I'm on an '05 instance. But yes that's what I meant. I think maybe I mistook what that value meant? –  Aushin Oct 29 '13 at 18:32
add comment

1 Answer

up vote 5 down vote accepted

The documentation for DBCC SHOW_STATISTICS says that Density (in the header) is:

Calculated as 1 / distinct values for all values in the first key column of the statistics object, excluding the histogram boundary values.

It also says:

This Density value is not used by the query optimizer and is displayed for backward compatibility with versions before SQL Server 2008.

You are using SQL Server 2005, so this value may be relevant to you in some circumstances, but I don't know exactly when 2005 would use that Density value.

share|improve this answer
    
Thanks, Paul. This explains it. –  Aushin Oct 30 '13 at 17:48
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.