Effects of SQL commands on statistics

The information stored in systabstats and sysstatistics is affected by data definition language (DDL). Some data modification language also affects systabstats. Table 6-10 summarizes how DDL affects the systabstats and sysstatistics tables.

Table 6-10: Effects of DDL on systabstats and sysstatistics

Command

Effect on systabstats

Effect on sysstatistics

alter table...lock

Changes values to reflect the changes to table and index structure and size.

When changing from allpages locking to data-only locking, the indid for clustered indexes is set to 0 for the table, and a new row is inserted for the index.

Same as create index, if changing from allpages to data-only locking or vice versa; no effect on changing between data-only locking schemes.

alter table to add, drop or modify a column definition

If the change affects the length of the row so that copying the table is required,

create table

Adds a row for the table. If a constraint creates an index, see the create index commands below.

No effect, unless a constraint creates an index. See the create index commands below.

create clustered index

For allpages-locked tables, changes indid to 1 and updates columns that are pertinent to the index; for data-only-locked tables, adds a new row.

Adds rows for columns not already included; updates rows for columns already included.

create nonclustered index

Adds a row for the nonclustered index.

Adds rows for columns not already included; updates rows for columns already included.

delete statistics

No effect.

Deletes all rows for a table or just the rows for a specified column.

drop index

Removes rows for nonclustered indexes and for clustered indexes on data-only-locked tables. For clustered indexes on allpages-locked tables, sets the indid to 0 and updates column values.

Does not delete actual statistics for the indexed columns. This allows the optimizer to continue to use this information.

Deletes simulated statistics for nonclustered indexes. For clustered indexes on allpages-locked tables, changes the value for the index ID in the row that contains simulated table data.

drop table

Removes all rows for the table.

Removes all rows for the table.

reorg

Updates restart points, if used with a time limit; updates number of pages and cluster ratios if page counts change; affects other values such as empty pages, forwarded or deleted row counts, depending on the option used.

The rebuild option recreates indexes.

truncate table

Resets values to reflect an empty table. Some values, like row length, are retained.

No effect; this allows reloading a truncated table without rerunning update statistics.

update statistics

table_name

Updates values for the table and for all indexes on the specified table.

Updates histograms for the leading column of each index on the table; updates the densities for all indexes and prefix subsets of indexes.

index_name

Updates values for the specified index.

Updates the histogram for the leading column of the specified index; updates the densities for the prefix subsets of the index.

column_name(s)

No effect.

Updates or creates a histogram for a column and updates or creates densities for the prefix subsets of the specified columns.

update index statistics

table_name

Updates values for the table and for all columns in all indexes on the specified table.

Updates histograms for all columns of each index on the table; updates the densities for all indexes and prefix subsets of indexes.

index_name

Updates values for the specified index

Updates the histogram for all column of the specified index; updates the densities for the prefix subsets of the index.

update all statistics

table_name

Updates values for the table and for all columns in the specified table.

Updates histograms for all columns on the table; updates the densities for all indexes and prefix subsets of indexes.