Database Reference
In-Depth Information
■
Note
each table in Object explorer in Management Studio has a
Statistics
node that shows the statistics defined in
the table.
The query returned three rows, as shown in Figure
3-5
.
Figure 3-5.
Column-level statistics: Result of the query
The first two rows correspond to the clustered and nonclustered indexes from the table. The last one, with the
name that starts with the
_WA
prefix, displays column-level statistics, which was created automatically when SQL Server
optimized our queries.
Let's examine it with the
DBCC SHOW_STATISTICS ('dbo.Customers', _WA_Sys_00000002_276EDEB3
) command.
As you can see in Figure
3-6
, it stores information about the data distribution for the
FirstName
column. As a result,
SQL Server can estimate the number of rows for first names, which we used as parameters, and generate different
execution plans for each parameter value.
Figure 3-6.
Column-level statistics: Auto-created statistics on the FirstName column
You can manually create statistics on a column or multiple columns with the
CREATE STATISTICS
command.
Statistics created on multiple columns are similar to statistics created on composite indexes. They include information
about multi-column density, although the histogram retains data distribution information for the leftmost column only.
