UPDATE STATISTICS Statement

Updates information about the distribution of key values in specified indexes.

Syntax

UPDATE STATISTICS [[database.]owner.]table_name [index_name]

where

table_name

Specifies the table with which the index is associated. The table_name parameter is required because Transact-SQL does not require index names to be unique in a database.

index_name

Specifies the index to be updated. If an index name 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 the sp_helpindex system stored procedure with the table name.

Remarks

SQL Server keeps statistics about the distribution of the key values in each index and uses these statistics in decisions about which index(es) to use in query processing. The optimization of your queries depends on the accuracy of the distribution steps. If there is significant change in the key values in your index, rerun UPDATE STATISTICS on that index. If a great deal of data in an indexed column has been added, changed, or removed (that is, if you suspect that the distribution of key values has changed) or the table has been truncated using the TRUNCATE TABLE statement and then repopulated, use the UPDATE STATISTICS statement.

UPDATE STATISTICS is run automatically when you create or re-create an index on a table that already contains data. However, to see when the statistics were last updated, use the STATS_DATE system function or the DBCC SHOW_STATISTICS option. For syntax and usage, see the Functions topic and the DBCC statement.

Permission

UPDATE STATISTICS permission defaults to the table owner and is not transferable.

Examples

A.    Update All Statistics for a Single Table

This single command will update the distribution statistics for all indexes on the authors table.

UPDATE STATISTICS authors

B.    Update Only the Statistics for a Single Index

To update only the distribution information for one index on a table, specify the index after the table name.

UPDATE STATISTICS authors au_id_ind

C.    Use Cursors to Run UPDATE STATISTICS on All Tables

In this example, the update_all_stats procedure will first create a cursor, tnames_cursor, and then populate the cursor with all rows from sysobjects of type 'U' = User-defined table. After retrieving all table names, this procedure will run UPDATE STATISTICS against each of the tables.

CREATE PROCEDURE update_all_stats
AS
/*
    This procedure will run UPDATE STATISTICS against
    all user-defined tables within this database.
*/
DECLARE @tablename varchar(30)
DECLARE @tablename_header varchar(75)
DECLARE tnames_cursor CURSOR FOR SELECT name FROM sysobjects 
    WHERE type = 'U'
OPEN tnames_cursor
FETCH NEXT FROM tnames_cursor INTO @tablename
WHILE (@@fetch_status <> -1)
BEGIN
    IF (@@fetch_status <> -2)
    BEGIN
        SELECT @tablename_header = "Updating "  
            RTRIM(UPPER(@tablename))
        PRINT @tablename_header
        EXEC ("UPDATE STATISTICS "  @tablename )
    END
    FETCH NEXT FROM tnames_cursor INTO @tablename
END
PRINT " "
PRINT " "
SELECT @tablename_header = "*************  NO MORE TABLES"
             "  *************" 
PRINT @tablename_header
PRINT " "
PRINT "Statistics have been updated for all tables."
DEALLOCATE tnames_cursor
go
update_all_stats
go
Updating AUTHORS
Updating DISCOUNTS
Updating EMPLOYEE
Updating JOBS
Updating PUB_INFO
Updating PUBLISHERS
Updating ROYSCHED
Updating SALES
Updating STORES
Updating TITLEAUTHOR
Updating TITLES
 
 
*************  NO MORE TABLES  *************
 
Statistics have been updated for all tables.

See Also