The update statistics commands create statistics if there are no statistics for a particular column, or replaces existing statistics. The statistics are stored in the system tables systabstats and sysstatistics. The syntax is:
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.
A new option in sp_configure is histogram tuning factor, which allows superior selection of the number of histogram steps. The default value for histogram tuning factor is 20. See the System Administration Guide for information about sp_configure.
