Effects of statistics on non-key column
The index always plays an important role, as far as the performance of the SELECT statement is concerned. Actually, the query optimizer first checks statistics of the predicate and then decides which index is supposed to be used. Generally, creating an index creates statistics on key columns of an index, by default, but it doesn't mean that statistics on non-key columns wouldn't get any benefit if it is available.
It is neither affordable nor desirable to have an index on each and every column of the table, or on all those columns that you use in predicate, because index comes with an overhead: it needs space to store itself as well as each DML statement update index.
Mostly, it is a good idea to have an ...
Get Microsoft SQL Server 2012 Performance Tuning Cookbook now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.
