The update statistics command helps Adaptive Server make the best decisions about which indexes to use when it processes a query, by keeping it up to date about the distribution of the key values in the indexes. Use update statistics when a large amount of data in an indexed column has been added, changed, or deleted.
When Component Integration Services is enabled, update statistics can generate accurate distribution statistics for remote tables. For more information, see the Component Integration Services User’s Guide.
Permission to issue the update statistics command defaults to the table owner and is not transferable. Its syntax is:
update statistics table_name [index_name]
If you do not specify an index name, the command updates the distribution statistics for all the indexes in the specified table. Giving an index name updates statistics for that index only.
You can find the names of indexes by using sp_helpindex. Here is how to list the indexes for the authors table:
sp_helpindex authors
index_name index_description index_keys index_max_rows_per_page
---------- ----------------- ---------- -----------------------
auidind clustered, unique au_id 0
aunmind nonclustered au_lname, 0
au_fname
(2 rows affected, return status = 0)
To update the statistics for all of the indexes, type:
update statistics authors
To update the statistics only for the index on the au_id column, type:
update statistics authors auidind
Because Transact-SQL does not require index names to be unique in a database, you must give the name of the table with which the index is associated. Adaptive Server runs update statistics automatically when you create an index on existing data.
