January 2, 2011 at 10:16 pm
I guess I have never given this much thought, but why would you create statistics on a non-indexed column? I know that stats are important in the selection of an appropriate execution plan/index, but if there is no index option, then why other with the stats.
I presume I am missing something obvious here......
January 2, 2011 at 10:29 pm
Remember that statistics are telling SQL Server what kind of data exists in this column for the entire table. If you use DBCC SHOWSTATISTICS on the statistic set, you will see what data is shown inside.
If the optimizer knows that the data is fairly unique in that column then it can use that data as to whether or not to scan the table or to use another index to get the other data and go after the non-indexed column in a Row-Lookup in the clustered index.
But you are right, it is all about getting the best query plan for performance and efficiency of the data cache and not pulling in too much.
January 2, 2011 at 10:31 pm
Generally you wouldn't. If auto-create stats is on and the optimiser thinks it would be useful to know data distribution of a column, SQL will create the stats by itself. You shouldn't have to.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply