They’re created at different points and, unless you’re creating the statistics manually yourself, they’re created slightly differently.The statistics on an index are created with the index.
Try as I might, I find it hard to over-emphasize the importance of statistics to SQL Server.
Bad or missing statistics leads to poor choices by the optimizer: The result is horrific performance.
This default applies only when the auto_create_statistics database option is set to ON, which is the default setting in the model database and thus in all new databases you create. To follow along, first make sure that auto_create_statistics is set to ON in the Adventure Works database, by running the following statement: Next, run the code in Listing 1, which creates the two tables in the database.
The examples I’ll employ in my discussion of column statistics use copies of the Person. You can see all statistics that exist on a specified table by using the sp_helpstats system procedure.
Each statistics object contains a histogram displaying the distribution of values of the column (or of the first column in the case of multi-column statistics).
Multi-column statistics also contains a correlation of values among the columns (called ), which are derived from the number of distinct rows or the column values.
The optimizer obtains its knowledge of the data, its distribution, and the number of rows a given query is likely to return from the available statistics.
Based on this knowledge, it decides the optimal access path, making choices such as whether to scan a table or perform an index seek, use a nested loop join or a hash join, and so on.
Lots of questions occur to people when I’m doing a presentation about statistics, and some get asked in front of the rest of the audience.
Then there are other questions that get asked later on, in conversation. There is no essential difference between the statistics on an index and the statistics on a table.
SQL Server Query Optimizer uses statistics to estimate the distribution of values in one or more columns of a table or index views, and the number of rows (called ) to create a high-quality query execution plan.