Home

	Expand Search

Transact-SQL User's Guide

Chapter 11 Creating Indexes on Tables

Updating statistics about indexes

Updating partition statistics

Updating partition statistics

Like the update statistics command for unpartitioned tables, the update partition statistics command helps Adaptive Server make the best decisions when it processes a query, by keeping it up to date about the number of pages within the partitions. Use update partition statistics when a large amount of data in a partitioned table has been added, changed, or deleted.

Permission to issue the update partiton statistics command defaults to the table owner and is not transferable. Its syntax is:

update partition statistics table_name

For example, suppose the authors table was partitioned as follows:

alter table authors partition 3

Then you run sp_helpartition to see how the partitions were distributed:

sp_helpartition authors

partitionid firstpage   controlpage ptn_data_pages
----------- ----------- ----------- --------------
          1         553         554              1
          2         817         816              1
          3        1009        1008              1
 
(3 rows affected, return status = 0)

Afterwards, you update the statistics for authors as follows:

update partition statistics authors

Using sp_helpartition on authors shows the following update:

partitionid firstpage   controlpage ptn_data_pages
----------- ----------- ----------- --------------
          1         553         554             10
          2         817         816              1
          3        1009        1008              1
 
(3 rows affected, return status = 0)

Dropping and re-creating a clustered index automatically redistributes the data within partitions and updates the partition statistics.