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

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

• CREATE STATISTICS statement
• DROP STATISTICS statement

Standards and compatibility

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;