Statistics in SQL Server - Microsoft SQL Server 2012 Performance Tuning - page 284

Here, in this example, histogram has shown two steps for the table Sales.
SalesOrderHeader , in two rows. It can show you a maximum of 200
steps. Inserting more rows into the table increases the range of steps and the
number of rows between two ranges. A histogram table gets updated as and
when statistics get updated.
Density:
A histogram can show you the distribution of values for the first leading column, whereas
Density Vector is used to measure cross-column correlation.
While making an execution plan, the query optimizer chooses the best-suited plan for a query
by looking at query selectivity. A column with high selectivity returns a small number of rows,
and one with low selectivity returns big result sets, which is why the query cost will be high in
the second scenario.
The query optimizer finds selectivity by looking at the density of the column used in the WHERE
and ON clauses of JOIN .
The relationship of selectivity with density is inverse; generally, a high-selectivity column would
have low density and a low-selectivity column would have high density.
Density is calculated with the following formula:
Density=1.00/Number of distinct value in column
Let us look at the density table returned by statistics, and also manually calculate the density
of columns, to tally it with the density returned by the statistics object:
--looking at the statistics of
--"PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID"
--this query will return 3 result sets
DBCC SHOW_STATISTICS ("Sales.SalesOrderDetail","PK_SalesOrderDetail_
SalesOrderID_SalesOrderDetailID")
GO
--manually confirming density of "SalesOrderID" column to
--match up with density of SalesOrderID column return by
--"DBCC SHOW_STATISTICS" (4th result set)
SELECT 1.000/ count (DISTINCT SalesOrderID) AS Manual_Density_
SalesOrderID FROM Sales.SalesOrderDetail
GO
--manually confirming density of "SalesOrderID" AND
--"SalesOrderDetailID" columns to match up with