Querying Data and Metadata from Partitioned Tables and Indexes
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.
Important
SQL Server does not guarantee completion of any queries that access a partitioned table if any files that belong to any filegroups of the table are not in an ONLINE state, regardless of which partitions are accessed by the query.
Using the $PARTITION Function
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
Using the Catalog Views
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