ALTER STATISTICS statement

Use this statement to control whether statistics are automatically updated on a column, or columns, in a table.

Syntax
ALTER STATISTICS 
[ ON ] table [ ( column1 [ , column2 ... ] ) ] 
AUTO UPDATE { ENABLE | DISABLE }
Parameters
  • ON   The word ON is optional. Including it has no impact on the execution of the statement.

  • AUTO UPDATE clause   Specify whether to enable or disable automatic updating of statistics for the column(s).

Remarks

During normal execution of queries, DML statements, and LOAD TABLE statements, the database server automatically maintains column statistics for use by the optimizer. The benefit of maintaining statistics for some columns may not outweigh the overhead necessary to generate them. For example, if a column is not queried often, or if it is subject to periodic mass changes that are eventually rolled back, there is little value in continually updating its statistics. Use the ALTER STATISTICS statement to suppress the automatic updating of statistics for these types of columns.

When automatic updating is disabled, you can still update the statistics for the column using the CREATE STATISTICS and DROP STATISTICS statements. However, you should only update them if it has been determined that it would have a positive impact on performance. Normally, column statistics should not be disabled.

Permissions

Must have DBA authority.

Side effects

If automatic updating has been disabled, the statistics may become out of date. Re-enabling will not immediately bring them up to date. Run the CREATE STATISTICS statement to recreate them, if necessary.

See also
Standards and compatibility
  • SQL/2003   Vendor extension.

Example

The following example disables the automatic updating of statistics on the Street column in the Customers table:

ALTER STATISTICS Customers ( Street ) AUTO UPDATE DISABLE;