Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

In a blog post from 2008 the famous Kalen Delaney wrote:

What is the difference between statistics and indexes? Do auto created statistics indicate that you should build an index?

Check out this the answer to this question at SQLCommunity.com

Unfortunately SQLCommunity.com is no longer. But the answer to this very question I would very like to know. Especially to the second one.

Update: An insightful article is here: http://msdn.microsoft.com/en-us/library/ms190397.aspx

share|improve this question

1 Answer 1

up vote 2 down vote accepted

No, they don't. If statistics are automatically created, it is because the optimizer can benefit from the single-column statistics of the cardinality estimates. It's simply an option. I'm guessing your real question may stem from statistics being automatically updated (as opposed to automatically create).

BOL definition for Statistics:

Statistics for query optimization are objects that contain statistical information about the distribution of values in one or more columns of a table or indexed view. The query optimizer uses these statistics to estimate the cardinality, or number of rows, in the query result.

There are many parameters for exactly WHEN statistics are marked as invalid (therefore updated when the next using query executes). Please see this blog post for a list of when this happens.

Take an example like this: You have a sequential table. You do 10 million sequential inserts at the end of the table. There is going to be zero fragmentation in that table. But the statistics will be forced as invalid (stale) and surely updated.

Therefore, the stats are updated. But there is no need to rebuild that clustered index (provided it's not a heap).

Often times, there will be inserts that aren't as sequential and this will cause page splits. So many inserts, much index fragmentation. Many inserts, stale statistics. So will stats be updated and the need for an index rebuild? Yes, absolutely.

But these two do not walk hand-in-hand.

share|improve this answer
    
Thank you. Someone is suggesting here, that because there are a lot of auto created statistics in the database, the indexes are wrong or missing. I'm trying to validate this opinion. So this question is more about auto-created statistics rather than about updates. So given your answer, the question is, if an optimizer can benefit from these single-column statistics, should not we be building indexes instead? –  zespri Apr 23 '12 at 2:21

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.