ALTER STATISTICS statement

Controls 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.

Privileges

You must be the table owner, or have one of the following privileges:

MANAGE ANY STATISTICS system privilege
ALTER ANY OBJECT system privilege

Side effects

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

Standards

ANSI/ISO SQL Standard
Not in the standard.

Example

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

ALTER STATISTICS GROUPO.Customers ( Street ) AUTO UPDATE DISABLE;