A Closer Look at "Statistics" in an SQL Server Sense--And Why They're So Important to Microsoft Dynamics NAV Users

There is the proverb "Never trust a statistic which you haven't faked yourself". Well, as SQL Server is as human as the next guy, this saying applies quite well ...

But what are these ominous statistics and what they are used for? The official definition for "statistics" in a SQL Server sense is: "Statistics for query optimization are objects that contain statistical information about the distribution of values in one or more columns of a table or indexed view".

Let's translate this into English. The "distribution of values in a column" actually mean, how many different values could be distinguished for that column. For example, if a "Sales Line" record has a field "Document Type" (values Quote, Order & Invoice) and contains 100 Quotes, 100 Orders and 100 Invoices (= total 300 records), a statistic on this "Document Type" would tell that the average distribution of the field values is about 33%. In SQL Server terms this would also be called the density of the values, in this example "Document Type" has a density of 0.33. The higher the density the more same values exist in the column/field, thus, a density of 1.00 would mean that all records have the same value in that column.

The "density" could be checked with the DBCC SHOW_STATISTICS command.

Another frequently used term according to this is "selectivity", which actually is quite the opposite of "density". A high "selectivity" means, that there are many different values in a column, hence the column has a low "density". Or the other way around: if ...