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