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:




  FROM PartitionTable



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.




 PartitionRowCount(Partition, NumberOfRowsInPartition) AS


   SELECT $PARTITION.myRangePF1(col1),


     FROM PartitionTable

   GROUP BY $PARTITION.myRangePF1(col1)



  (partition_scheme_id, Partition, data_space_id, DataSpaceName) AS


   SELECT dds.partition_scheme_id,



     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;



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