table_or_indexed_view_name

Name of the table or indexed view to update statistics on.

index_or_statistics_name

Name of the index to update statistics on or name of the statistics to update. If index_or_statistics_name is not specified, the query optimizer updates all statistics for the table or indexed view. 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.

FULLSCAN

Compute statistics by scanning all rows in the table or indexed view. FULLSCAN and SAMPLE 100 PERCENT have the same results. FULLSCAN cannot be used with the SAMPLE option.

SAMPLE number { PERCENT | ROWS }

Approximate percentage or number of rows in the table or indexed view for the query optimizer to use when it updates statistics. For PERCENT, number can be from 0 through 100 and for ROWS, number can be from 0 to the total number of rows. When 0 PERCENT or 0 ROWS is specified, the statistics object is created but does not contain statistics data.

The actual percentage or number of rows the query optimizer samples might not match the percentage or number specified for the following reasons: the query optimizer samples enough data pages to ensure a statistically significant sample of data and the query optimizer scans all rows on a data page.

SAMPLE is useful for special cases where the query plan, based on default sampling, is not optimal. In most situations, it is not necessary to specify SAMPLE because the query optimizer uses sampling and determines the statistically significant sample size by default, as required to create high-quality query plans.

SAMPLE cannot be used with the FULLSCAN option. When neither SAMPLE nor FULLSCAN is specified, the query optimizer uses sampled data and computes the sample size by default.

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.

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.

Requires ALTER permission on the table or view.

A. Updating all statistics for a single table

USE AdventureWorks;
GO
UPDATE STATISTICS Sales.SalesOrderDetail;
GO

B. Updating only the statistics for a single index

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

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

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

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

Reference

Other Resources

Help and Information