Creating statistics on unindexed columns can improve the performance of many queries. The optimizer can use statistics on any column in a where or having clause to help estimate the number of rows from a table that match the complete set of query clauses on that table.
Adding statistics for the minor columns of indexes and for unindexed columns that are frequently used in search arguments can greatly improve the optimizer’s estimates.
Maintaining a large number of indexes during data modification can be expensive. Every index for a table must be updated for each insert and delete to the table, and updates can affect one or more indexes.
Generating statistics for a column without creating an index gives the optimizer more information to use for estimating the number of pages to be read by a query, without entailing the processing expense of index updates during data modification.
The optimizer can apply statistics for any columns used in a search argument of a where or having clause and for any column named in a join clause. You need to determine whether the expense of creating and maintaining the statistics on these columns is worth the improvement in query optimization.
The following commands create and maintain statistics:
update statistics, when used with the name of a column, generates statistics for that column without creating an index on it.
The optimizer can use these column statistics to more precisely estimate the cost of queries that reference the column.
update index statistics, when used with an index name, creates or updates statistics for all columns in an index.
If used with a table name, it updates statistics for all indexed columns.
update all statistics creates or updates statistics for all columns in a table.
Good candidates for column statistics are:
Columns frequently used as search arguments in where and having clauses
Columns included in a composite index, and which are not the leading columns in the index, but which can help estimate the number of data rows that need to be returned by a query.
See “How scan and filter selectivity can differ” for information on how additional column statistics can be used in query optimization.
