How do you know if the Query Optimizer has automatically created column statistics on a column in a table? Actually, this is quite easy to find out. This is not always the case, so you will need to perform some testing - before and after an index is added - to see if adding an index actually helps or not. In other words, if the Query Optimizer thinks that column statistics are useful, there is also a good chance that adding an appropriate index to this same column would be useful. It is a clue to the potential need for an index on the column. The addition of an automatically added statistics to a column is also useful for something else.

This is a good thing as it helps provide better information to the Query Optimizer so that better optimized execution plans are created to execute queries. If your databases are set for “Auto Create Statistics,” the SQL Server Query Optimizer, when running queries, will consider if there is any benefit to adding statistics for any column that doesn’t already have statistics for it. Only by testing different indexing strategies can you be sure that you have fully optimized your database. Because of this, it is a good idea to test out various indexing strategies for the most common queries run against your database. As with any general guideline, there are exceptions. All the tips provided on this website about indexing are general guidelines.

Sql server indexing