Effects of SQL commands on statistics

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

Table 2-8: 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 you change tables and indexes 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 you are changing from allpages to data-only locking or back, no effect on changing between data-only locking schemes.

alter table to add, drop or modify a column definition

Some alter table parameters (for example, using drop column, adding non-NULL columns with add, or using modify to decrease the length of variable-length columns) require a data copy of the table. Other alter table operations are done with an update of the system catalog information.

If the change affects the length of the row and alter table must copy the table, the alter table parameter changes the values to reflect the changes to table and index structures and size.

If the alter table parameter does not perform a data copy, then no changes are done.

If the change requires a data copy, alter table rebuilds all indexes, and has the same effect as create clustered or create non-clustered index. alter table does not affect the statistics row maintained on nonindexed columns, but it does delete statistics rows on columns being dropped.

If the change does not perform a data copy, no changes are done.

create table

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

Adds a row for the table. If 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 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 re-creates 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 re-running update statistics.

update statistics (Running update statistics on an empty table does not affect the system tables.)

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 densities for all indexes and prefix subsets of indexes.