Updates information about the distribution of key values in specified indexes.
UPDATE STATISTICS [[database.]owner.]table_name [index_name]
where
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.
UPDATE STATISTICS permission defaults to the table owner and is not transferable.
This single command will update the distribution statistics for all indexes on the authors table.
UPDATE STATISTICS authors
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
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.
CREATE INDEX | EXECUTE |
Cursors | sp_helpindex |
DBCC | System Functions |