The update statistics commands create statistics, if there are no statistics for a particular column, or replaces existing statistics if they already exist. The statistics are stored in the system tables systabstats and sysstatistics. The syntax is:
update statistics table_name
[ [index_name] | [( column_list ) ] ]
[using step values ]
[with consumers = consumers ]
update index statistics table_name [index_name]
[using step values ]
[with consumers = consumers ]
update all statistics table_name
The effects of the commands and their parameters are:
For update statistics:
table_name – Generates statistics for the leading column in each index on the table.
table_name index_name – Generates statistics for all columns of the index.
table_name (column_name) – Generates statistics for only this column.
table_name (column_name, column_name...) – Generates a histogram for the leading column in the set, and multi column density values for the prefix subsets.
using step values – Identifies the number of steps used. The default is 20 steps. If you need to change the default number of steps, use sp_configure.
For update index statistics:
table_name – Generates statistics for all columns in all indexes on the table.
table_name index_name – Generates statistics for all columns in this index.
For update all statistics:
table_name – Generates statistics for all columns of a table.
using step values – Identifies the number of steps used. The default is 20 steps. If you need to change the default number of steps, use sp_configure.
