If you’ve previously created a partitioned table in SQL Server, and you now want to know how many rows are being stored in each partition, here are three queries you can use.

In particular, you can:

• Query the sys.dm_db_partition_stats view
• Query the sys.partitions view
• Use the $PARTITION function in a query

Below are examples of all three.

sys.dm_db_partition_stats

The sys.dm_db_partition_stats system dynamic management view returns page and row count information for every partition in the current database.

Here’s an example of returning the row count of a partitioned table called Movies.

SELECT
    partition_number,
    row_count
FROM sys.dm_db_partition_stats
WHERE object_id = OBJECT_ID('Movies');

Result:

+--------------------+-------------+
| partition_number   | row_count   |
|--------------------+-------------|
| 1                  | 0           |
| 2                  | 100         |
| 3                  | 3979        |
| 4                  | 0           |
+--------------------+-------------+

In this case, the first and last partitions are empty (as recommended by Microsoft).

sys.partitions

The sys.partitions system catalog view contains a row for each partition of all the tables and most types of indexes in the database.

Here’s how we can use it to return the same data as in the previous example.

SELECT
    partition_number,
    rows
FROM sys.partitions
WHERE object_id = OBJECT_ID('Movies');

Result:

+--------------------+--------+
| partition_number   | rows   |
|--------------------+--------|
| 1                  | 0      |
| 2                  | 100    |
| 3                  | 3979   |
| 4                  | 0      |
+--------------------+--------+

You might notice that this is almost identical to the previous query. The only difference is that this one uses the column called rows, whereas the previous one used a column called row_count.

$PARTITIONS

The $PARTITIONS system function returns the partition number into which a set of partitioning column values would be mapped for any specified partition function.

We can therefore use this info to construct a query that returns row count data.

SELECT 
    $PARTITION.MoviesPartitionFunction(MovieId) AS [Partition Number],   
    COUNT(*) AS [Number of Rows] 
FROM Movies   
GROUP BY $PARTITION.MoviesPartitionFunction(MovieId)
ORDER BY [Partition Number] ASC

Result:

+--------------------+------------------+
| Partition Number   | Number of Rows   |
|--------------------+------------------|
| 2                  | 100              |
| 3                  | 3979             |
+--------------------+------------------+

The only difference with the result of this example, is that it only returns the count of non-empty rows.