UPDATE STATISTICS (T-SQL)

Updates information about the distribution of key values for one or more statistics groups (collections) in the specified table. UPDATE STATISTICS is run automatically when an index is created on a table that already contains data. To create statistics on columns, see CREATE STATISTICS.

Syntax

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

Arguments

table: Is the name of the table for which to update statistics. Table names must conform to the rules for identifiers. For more information, see Using Identifiers. Because index names are not unique within each database, table must be specified. Specifying the database or table owner is optional.
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 are updated. To see a list of index names and descriptions, execute sp_helpindex with the table 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.
n: Is a placeholder indicating that multiple statistics_name groups can be specified.
FULLSCAN: Specifies that all rows in table 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 the number of rows to sample when collecting statistics for larger tables. To use the default sampling behavior for larger tables, 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.

Note The default behavior is to perform a sample scan on the target table. SQL Server automatically computes the required sample size.
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 never automatically rebuild statistics and disables 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.

Permissions

UPDATE STATISTICS permissions default to the table 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