Transact-SQL Reference

UPDATE STATISTICS

Updates information about the distribution of key values for one or more statistics groups (collections) in the specified table or indexed view. To create statistics on columns, see CREATE STATISTICS.

Syntax

UPDATE STATISTICS table | view
    [
        index
        | ( statistics_name [ ,...n ] )
    ]
    [    WITH
        [
            [ FULLSCAN ]
            | SAMPLE number { PERCENT | ROWS } ]
            | RESAMPLE
        ]
        [ [ , ] [ ALL | COLUMNS | INDEX ]
        [ [ , ] NORECOMPUTE ]
    ]

Arguments

table | view

Is the name of the table or indexed view for which to update statistics. Table or view names must conform to the rules for identifiers. For more information, see Using Identifiers. Because index names are not unique within each database, table or view must be specified. Specifying the database, table, or view owner is optional. Indexed views are supported only on Microsoft® SQL Server™ 2000, Enterprise Edition.

index

Is the index for which statistics are being updated. Index names must conform to the rules for identifiers. If index is not specified, the distribution statistics for all indexes in the specified table or indexed view are updated. To see a list of index names and descriptions, execute sp_helpindex with the table or view name.

statistics_name

Is the name of the statistics group (collection) to update. Statistics names must conform to the rules for identifiers. For more information about creating statistics groups, see CREATE STATISTICS.

Is a placeholder indicating that multiple statistics_name groups can be specified.

FULLSCAN

Specifies that all rows in table or view should be read to gather the statistics. FULLSCAN provides the same behavior as SAMPLE 100 PERCENT. FULLSCAN cannot be used with the SAMPLE option.

SAMPLE number { PERCENT | ROWS }

Specifies the percentage of the table or indexed view, or the number of rows to sample when collecting statistics for larger tables or views. Only integers are allowed for number whether it is PERCENT or ROWS. To use the default sampling behavior for larger tables or views, use SAMPLE number with PERCENT or ROWS. Microsoft SQL Server ensures a minimum number of values are sampled to ensure useful statistics. If the PERCENT, ROWS, or number option results in too few rows being sampled, SQL Server automatically corrects the sampling based on the number of existing rows in the table or view.

Note The default behavior is to perform a sample scan on the target table or indexed view. SQL Server automatically computes the required sample size.

RESAMPLE

Specifies that statistics will be gathered using an inherited sampling ratio for all existing statistics including indexes. If the sampling ratio results in too few rows being sampled, SQL Server automatically corrects the sampling based on the number of existing rows in the table or view.

ALL | COLUMNS | INDEX

Specifies whether the UPDATE STATISTICS statement affects column statistics, index statistics, or all existing statistics. If no option is specified, the UPDATE STATISTICS statement affects all statistics. Only one type (ALL, COLUMNS, or INDEX) can be specified per UPDATE STATISTICS statement.

NORECOMPUTE

Specifies that statistics that become out of date are not automatically recomputed. Statistics become out of date depending on the number of INSERT, UPDATE, and DELETE operations performed on indexed columns. When specified, this option causes SQL Server to disable automatic statistics rebuilding. To restore automatic statistics recomputation, reissue UPDATE STATISTICS without the NORECOMPUTE option or execute sp_autostats.

Important Disabling automatic statistics recomputation can cause the SQL Server query optimizer to choose a less optimal strategy for queries that involve the specified table.

Remarks

SQL Server keeps statistics about the distribution of the key values in each index and uses these statistics to determine which index(es) to use in query processing. Users can create statistics on nonindexed columns by using the CREATE STATISTICS statement. Query optimization depends on the accuracy of the distribution steps:

If there is significant change in the key values in the index, rerun UPDATE STATISTICS on that index.
If a large amount of data in an indexed column has been added, changed, or removed (that is, if the distribution of key values has changed), or the table has been truncated using the TRUNCATE TABLE statement and then repopulated, use UPDATE STATISTICS.

To see when the statistics were last updated, use the STATS_DATE function.

Statistics can be created or updated on tables with computed columns only if the conditions are such that an index can be created on these columns. For more information about the requirements and restrictions on creating indexes on computed columns, see CREATE INDEX.

Permissions

UPDATE STATISTICS permissions default to the table or view owner, and are not transferable.

Examples

A. Update all statistics for a single table

This example updates the distribution statistics for all indexes on the authors table.

UPDATE STATISTICS authors

B. Update only the statistics for a single index

This example updates only the distribution information for the au_id_ind index of the authors table.

UPDATE STATISTICS authors au_id_ind

C. Update statistics for specific statistics groups (collections) using 50 percent sampling

This example creates and then updates the statistics group for the au_lname and au_fname columns in the authors table.

CREATE STATISTICS anames 
   ON authors (au_lname, au_fname)
   WITH SAMPLE 50 PERCENT
GO
-- Time passes. The UPDATE STATISTICS statement is then executed.
UPDATE STATISTICS authors(anames) 
   WITH SAMPLE 50 PERCENT
GO

D. Update statistics for a specific statistics groups (collections) using FULLSCAN and NORECOMPUTE

This example updates the anames statistics group (collection) in the authors table, forces a full scan of all rows in the authors table, and turns off automatic statistics updating for the statistics group (collection).

UPDATE STATISTICS authors(anames)
   WITH FULLSCAN, NORECOMPUTE
GO