Does a re-index update statistics?

by Thor Erik   Last Updated September 14, 2018 15:06 PM

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.



Answers 2


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

MicSim
MicSim
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.

Source: https://docs.microsoft.com/en-us/sql/relational-databases/statistics/statistics?view=sql-server-2017#after-maintenance-operations

bside
bside
September 14, 2018 14:39 PM

Related Questions


Ola Hallengren Index Script not Reindexing

Updated August 12, 2015 19:02 PM

Updating statistics on Very large 18 TB database

Updated March 04, 2017 02:06 AM



Index Rebuild on database

Updated July 03, 2018 17:06 PM