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.

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

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.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.**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.**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}

August 30, 2013 08:02 AM

Operations such as rebuilding, defragmenting, or reorganizing an index do not change the distribution of data. Therefore,

you do not need to update statistics after performing ALTER INDEX REBUILD, DBCC DBREINDEX, DBCC INDEXDEFRAG, or ALTER INDEX REORGANIZE operations. The Query Optimizer updates statistics when you rebuild an index on a table or view with ALTER INDEX REBUILD or DBCC DBREINDEX, however this statistics update is a byproduct of re-creating the index. The Query Optimizer does not update statistics after DBCC INDEXDEFRAG or ALTER INDEX REORGANIZE operations.

September 14, 2018 14:39 PM

- Serverfault Help
- Superuser Help
- Ubuntu Help
- Webapps Help
- Webmasters Help
- Programmers Help
- Dba Help
- Drupal Help
- Wordpress Help
- Magento Help
- Joomla Help
- Android Help
- Apple Help
- Game Help
- Gaming Help
- Blender Help
- Ux Help
- Cooking Help
- Photo Help
- Stats Help
- Math Help
- Diy Help
- Gis Help
- Tex Help
- Meta Help
- Electronics Help
- Stackoverflow Help
- Bitcoin Help
- Ethereum Help