Partilhar via

UPDATE STATISTICS (Transact-SQL)

  • Artigo

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 (Transact-SQL).

Topic link iconTransact-SQL Syntax Conventions

Syntax

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

<update_stats_stream_option> ::=
    [ STATS_STREAM = stats_stream ]
    [ ROWCOUNT = numeric_constant ]
    [ PAGECOUNT = numeric contant ]

Arguments

  • table | view
    Is the name of the table or indexed view for which to update statistics. Table or view names must comply with the rules for identifiers. For more information, see Using Identifiers As Object Names. Because index names are not unique within each database, table or view must be specified. Specifying the database, table, or view schema is optional.

  • index
    Is the index for which statistics are being updated. Index names must comply with the rules for identifiers. If index is not specified, all distribution statistics in the specified table or indexed view are updated. This includes statistics created using the CREATE STATISTICS statement, autocreated statistics, and statistics created as a byproduct of index creation.

    To display a report on the indexes of an object, execute sp_helpindex and specify the table or view name.

  • statistics_name
    Is the name of the statistics group (collection) to update. Statistics names must comply with the rules for identifiers. For more information about creating statistics groups, see CREATE STATISTICS (Transact-SQL).
  • 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. number must be an integer, whether it is PERCENT or ROWS. To use the default sampling behavior for larger tables or views, use SAMPLE number with PERCENT or ROWS. The SQL Server 2005 Database Engine makes sure that a minimum number of values are sampled to guarantee useful statistics. If the PERCENT, ROWS, or number option causes too few rows to be sampled, the Database Engine automatically corrects the sampling based on the number of existing rows in the table or view. At least 1,000 data pages, approximately, are sampled. If the PERCENT, ROWS, or number option creates more values than are needed for a useful sample, the Database Engine tries to match the requested sample amount. However, because samples are taken by scanning all data pages, the actual sample size may not be exactly the same as the amount specified. When 0 PERCENT or ROWS is specified, the result is an empty statistics set.

    Note

    The default behavior is to perform a sample scan on the target table or indexed view. The Database Engine 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 creates too few rows being sampled, the Database Engine 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 the Database Engine to disable automatic statistics rebuilding. To restore automatic statistics recomputation, reissue UPDATE STATISTICS without the NORECOMPUTE option or run sp_autostats

    Important

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

  • <update_stats_stream_option>
    Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.

Remarks

The Database Engine keeps statistics about the distribution of the key values in each index and uses these statistics to determine which index or indexes 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 lots 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 by using the TRUNCATE TABLE statement and then repopulated, use UPDATE STATISTICS.

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

UPDATE STATISTICS WITH RESAMPLE updates all the statistics on a table at the current sampling rate. This means that statistics tied to indexes, which are created with full scan when the index is built, require the whole table scan to be refreshed. This potentially can be a very time consuming operation, especially when it involves large partitioned tables with many indexes. Refreshing each statistic requires reading lots of data. To avoid this problem, consider using sp_updatestats (Transact-SQL). This updates statistics only when they are required.

Statistics can be created or updated on tables with computed columns only if the conditions exist in which an index can be created on these columns. For more information about the requirements and restrictions for creating indexes on computed columns, see CREATE INDEX (Transact-SQL).

If you disable automatic statistics recomputation, you must manually update the statistical information.

Note

The UPDATE STATISTICS statement re-enables automatic statistical updating on the target table or view unless the NORECOMPUTE clause is specified.

Permissions

Requires ALTER permission on the table or view.

Examples

A. Updating all statistics for a single table

The following example updates the distribution statistics for all indexes on the SalesOrderDetail table.

USE AdventureWorks;
GO
UPDATE STATISTICS Sales.SalesOrderDetail;
GO

B. Updating only the statistics for a single index

The following example updates only the distribution information for the AK_SalesOrderDetail_rowguid index of the SalesOrderDetail table.

USE AdventureWorks;
GO
UPDATE STATISTICS Sales.SalesOrderDetail AK_SalesOrderDetail_rowguid;
GO

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

The following example creates and then updates the statistics group for the Name and ProductNumber columns in the Product table.

USE AdventureWorks;
GO
CREATE STATISTICS Products
    ON Production.Product ([Name], ProductNumber)
    WITH SAMPLE 50 PERCENT
-- Time passes. The UPDATE STATISTICS statement is then executed.
UPDATE STATISTICS Production.Product(Products) 
    WITH SAMPLE 50 PERCENT;

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

The following example updates the Products statistics group (collection) in the Product table, forces a full scan of all rows in the Product table, and turns off automatic statistics updating for the statistics group (collection).

USE AdventureWorks;
GO
UPDATE STATISTICS Production.Product(Products)
    WITH FULLSCAN, NORECOMPUTE;
GO

See Also

Reference

ALTER DATABASE (Transact-SQL)
CREATE INDEX (Transact-SQL)
CREATE STATISTICS (Transact-SQL)
sys.stats (Transact-SQL)
sys.stats_columns (Transact-SQL)
Cursors (Transact-SQL)
DBCC SHOW_STATISTICS (Transact-SQL)
DROP STATISTICS (Transact-SQL)
EXECUTE (Transact-SQL)
EVENTDATA (Transact-SQL)
sp_autostats (Transact-SQL)
sp_createstats (Transact-SQL)
sp_helpindex (Transact-SQL)
sp_updatestats (Transact-SQL)
STATS_DATE (Transact-SQL)

Other Resources

Functions (Database Engine)

Help and Information

Getting SQL Server 2005 Assistance

Change History

Release History

12 December 2006
Changed content:
  • Removed the statement "UPDATE STATISTICS is not allowed in an explicit or implicit transaction." from the Remarks. This restriction is not applicable to SQL Server 2005.