update table statistics

Description

update table statistics updates statistics that are stored in systabstats table, such as rowcount, cluster ratios, and so on. update table statistics does not affect column statistics stored in sysstatistics.

Syntax

 update table statistics table_name
	[partition data_partition_name]
	[index_name [partition index_partition_name]]

Parameters

Examples

Example 1

Performs a table statistics update on the smallsales partition:

update table statistics titles partition smallsales

Example 2

Performs a table statistics update on all of the partitions in the titles table:

update table statistics titles

Usage

• update table statistics does not update statistics for index partitions. To generate table-level statistics for index partitions, use update statistics.

• Because running update table statistics incurs the I/O cost of running update statistics, use update statistics to generate both column and table statistics.

  You can create, and then drop, a global index to generate global statistics.

  When you run update statistics on a single partition, you create global statistics by merging partition statistics. However, these merged global statistics are less accurate than the global statistics created as a side-effect of global index creation. Avoid generating column statistics that overwrite more accurate, earlier versions of column statistics.

  When you specify:

  • index_name – update table statistics updates statistics for all the index partitions of the index.

  • index_partition – update table statistics updates statistics for the specific index partition.

Standards

ANSI SQL – Compliance level: Transact-SQL extension.

Permissions

update table 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