Lately I met a problem with partitioned tables. I needed a report on number of rows of a partitioned table in different filegroups. It is quite easy to find number of rows in partitions with the following query:

 

SELECT $PARTITION.myRangePF1(col1),

       COUNT(*)

  FROM PartitionTable

GROUP BY $PARTITION.myRangePF1(col1)

 

However, finding number of rows in different filegroups was a bit harder. I managed to do it with the query bellow. I am not sure this is the best way, and that this query works in any situation. I hope it is going to be useful for somebody, and I would appreciate feedback.

 

 

WITH

 PartitionRowCount(Partition, NumberOfRowsInPartition) AS

  (

   SELECT $PARTITION.myRangePF1(col1),

          COUNT(*)

     FROM PartitionTable

   GROUP BY $PARTITION.myRangePF1(col1)

  ),

 PartitionsDataSpaces

  (partition_scheme_id, Partition, data_space_id, DataSpaceName) AS

  (

   SELECT dds.partition_scheme_id,

          dds.destination_id,

          dds.data_space_id,

          ds.name

     FROM sys.destination_data_spaces dds

          INNER JOIN sys.indexes i

           ON dds.partition_scheme_id = i.data_space_id

          INNER JOIN sys.data_spaces ds

           ON dds.data_space_id = ds.data_space_id

    WHERE i.object_id = OBJECT_ID(‘PartitionTable’)   

          AND i.index_id < 2

  )

SELECT pds.DataSpaceName,

       SUM(prc.NumberOfRowsInPartition) AS NumberOfRowsInDataSpace

  FROM PartitionRowCount prc

       INNER JOIN PartitionsDataSpaces pds

        ON prc.Partition = pds.Partition

GROUP BY pds.DataSpaceName;

GO

 

Dejan Sarka

Independent Consultant at Dejan Sarka S.P.
I am a trainer and consultant that focus on development of database & business intelligence applications.

Besides projects, I spend about half of the time on training and mentoring. I am the founder of the Slovenian SQL Server and .NET Users Group. and the main author or coauthor of eleven books about databases and SQL Server. I also developed many courses and seminars for SolidQ.
Dejan Sarka