January 24, 2008 at 6:17 am
1> When i say Auto create statistics, what are the columns for which statistics are collected? Are these all columns part of the primary key or just the index statistics?
2> What is the difference between index statistics and column statistics?
3> Do i need to explicitly create statistics for columns which are not part of primary key but are used in queries?
4> Who are candidates for column statistics?
5> If i explicitly create statistics on some columns, is updating them my resposibility or its taken care of by auto update statistics?
6> Which system table stores statistics information?
January 24, 2008 at 7:01 am
syed muhammad naveed (1/24/2008)
1> When i say Auto create statistics, what are the columns for which statistics are collected? Are these all columns part of the primary key or just the index statistics?
First column of each index, and any other columns that the optimiser thinks it might be useful to have stats on. i believe that stats on indexes are always created, regardless of the setting of auto-create stats. Suto create stats just affects the creation of column statistics by the optimiser.
2> What is the difference between index statistics and column statistics?
Not much. Just that column stats are on columns that are not in an index.
3> Do i need to explicitly create statistics for columns which are not part of primary key but are used in queries?
Generally, no. If the optimiser thinks it will be useful to have stats, it will create them. (If auto create statistics is on)
4> Who are candidates for column statistics?
Columns used in joins or where clause that don't have an index on them
5> If i explicitly create statistics on some columns, is updating them my resposibility or its taken care of by auto update statistics?
Taken care of by the auto update
6> Which system table stores statistics information?
Included in sysindexes, I think (in SQL 2000. in 2005, I haven't found them yet)
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 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply