The update statistics commands create statistics, if there are none for a particular column, or replaces existing statistics. Statistics are stored in the systabstats and sysstatistics system tables:
update statistics table_name [[ partition data_partition_name ] [ (column_list ) ] | index_name [ partition index_partition_name ] ] [ using step values ] [ with consumers = consumers] [, sampling=percent] update index statistics table_name [[ partition data_partition_name ] | [ index_name [ partition index_partition_name ] ] ] [ using step values ] [ with consumers = consumers] [, sampling=percent] update all statistics table_name [ partition data_partition_name ] [ sp_configure histogram tuning factor, <value> update table statistics table_name [partition data_partition_name ] delete [ shared ] statistics table_name [ partition data_partition_name ] [( column_name[, column_name ] ...)]
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.
partition_name – generates statistics for only this partition.
partition_name table_name (column_name) – generates statistics for this column of this table on this partition.
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 multicolumn density values for the prefix subsets.
using step values – identifies the number of steps used. The default is 20 steps. To change the default number of steps, use sp_configure.
sampling = percent – the numeric value of the sampling percentage, such as 05 for 5%, 10 for 10%, and so on. The sampling integer is between zero (0) and one hundred (100).
For update index statistics:
table_name – generates statistics for all columns in all indexes on the table.
partition_name table_name – generates statistics for all columns in all indexes for the table on this partition.
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.
table_name partition_name – generates statistics for all columns of a table on a partition.
using step values – identifies the number of steps used. The default is 20 steps. To change the default number of steps, use sp_configure.
sp_configure syntax includes the histogram tuning factor, which allows a greater selection of the number of histogram steps. The default value for histogram tuning factor is 20. See Chapter 5,4“Setting Configuration Parameters” in System Administration Guide: Volume 1 for information about sp_configure.
