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.

I've been doing the MS10775A course this past week and one question that came up that the trainer couldn't answer reliably is:

Does a re-index update the statistics?

We found discussions online arguing both that it does and that it doesn't.

share|improve this question
add comment

2 Answers

up vote 6 down vote accepted

You can keep the following in mind when caring about updating statistics (copied from Rebuilding Indexes vs. Updating Statistics (Benjamin Nevarez)

  1. By default, the UPDATE STATISTICS statement uses only a sample of records of the table. Using UPDATE STATISTICS WITH FULLSCAN will scan the entire table.

  2. By default, the UPDATE STATISTICS statement updates both index and column statistics. Using the COLUMNS option will update column statistics only. Using the INDEX option will update index statistics only.

  3. Rebuilding an index, for example by using ALTER INDEX … REBUILD will also update index statistics with the equivalent of using WITH FULLSCAN unless the table is partitioned, in which case the statistics are only sampled* (applies to SQL Server 2012 and later). Rebuilding indexes does not update column statistics.

  4. Reorganizing an index, for example using ALTER INDEX … REORGANIZE does not update any statistics.

The short answer is that you need to use UPDATE STATISTICS to update column statistics and that an index rebuild will update only index statistics.

* Added by Paul White

share|improve this answer
add comment

Updates Statistics will update the stats of those columns which are not part of indexes.

If you are looking for a script then you can find one on the following link.

http://www.mytechmantra.com/LearnSQLServer/Script-to-Update-Statistics-for-all-SQL-Server-Databases

share|improve this answer
add comment

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.