In pre-11.9 versions of SQL Server and Adaptive Server, dropping an index removes the distribution page for the index. In version 11.9.2, maintaining column-level statistics is under explicit user control, and the optimizer can use column-level statistics even when an index does not exist. The delete statistics command allows you to drop statistics for specific columns.
If you create an index and then decide to drop it because it is not useful for data access, or because of the cost of index maintenance during data modifications, you need to determine:
Whether the statistics on the index are useful to the optimizer.
Whether the distribution of key values in the columns for this index are subject to change over time as rows are inserted and deleted.
If the distribution of key values changes, you need to run update statistics periodically to maintain useful statistics.
This example command deletes the statistics for the price column in the titles table:
delete statistics titles(price)
The delete
statistics command, when used with a table name, removes
all statistics for a table, even where indexes exist.
You must run update statistics on the table to restore the statistics for the index.
