Export (0) Print
Expand All

Querying Data and Metadata from Partitioned Tables and Indexes

Updated: 12 December 2006

When querying data or performing updates, there is no difference in the way you reference a partitioned table versus a table that is not partitioned.

ms187924.note(en-US,SQL.90).gifImportant:
SQL Server does not guarantee completion of any queries that access a partitioned table if any files belonging to any filegroups of the table are not in an ONLINE state, regardless of which partitions are accessed by the query.

To focus queries on individual partitions, you can use the $PARTITION function together with the partition function name.

You can do the following by using $PARTITION:

  • Access all rows in a subset of partitions of a partitioned table.
  • Examine how many rows exist in each partition.
  • Determine in which partition a row with a particular partition key value resides, or where it would be inserted.

To query individual partitions of a partitioned table or index

The following catalog views contain partitioning information at the database, table, and index level, and also information about individual partition functions and partition schemes.

To get information about individual partition functions

To get information about individual parameters of partition functions

To get information about the boundary values of a partition function

To get information about all the partition schemes in a database

To get information about individual partition schemes

To get information about all the partitions in a database

To get partitioning information about a table or index

Release History

12 December 2006

New content:
  • Added the Important note about querying partitioned tables that comprise files that are not ONLINE.

Community Additions

ADD
Show:
© 2014 Microsoft