update statistics

Description

Updates information about the distribution of key values in specified indexes or for specified columns, for all columns in an index or for all columns in a table.

Syntax

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 ]

Parameters

table_name

When used with update statistics, table_name is the name of the table with which the index is associated. table_name is required, since Transact-SQL does not require index names to be unique in a database.

index_name

is the name of the index to be updated. If an index name is not specified, the distribution statistics for all the indexes in the specified table are updated.

column_list

is a comma-separated list of columns.

using step values

specifies the number of histogram steps. The default value is 20, for columns where no statistics exist. If you need to change the default for the this, use sp_configure. If statistics for a column already exist in sysstatistics, the default value is the current number of steps.

with consumers = consumers

specifies the number of consumer processes to be used for a sort when column_list is provided and parallel query processing is enabled.

index

specifies that statistics for all columns in an index are to be updated.

Examples

Example 1

Generates statistics for the price column of the titles table:

update statistics titles (price) using 40 values

Example 2

Generates statistics for all columns in all indexes of the authors table:

update index statistics authors

Example 3

Generates statistics for all columns in the au_names_ix index of the authors table:

update index statistics authors au_names_ix

Usage


create index and stored procedures

Adaptive Server automatically recompiles stored procedures after executing update statistics statements. Although adhoc queries that you start before executing update statistics still continue to work, they do not take advantage of the new statistics.In Adaptive Server versions 12.5 and earlier, update statistics was ignored by cached stored procedures.

Standards

ANSI SQL – Compliance level: Transact-SQL extension.

Permissions

update statistics permission defaults to the table owner and is not transferable. The command can also be executed by the Database Owner, who can impersonate the table owner by running the setuser command.

See also

Commandsdelete statistics