SQL Server Partition Details

by Amol 29. April 2010 12:26

You will find the following query useful if you deal with SQL Server Partitioned objects. I have written this query to know whether paritioned data gets instered into desired partition. Apart from this you will get to know partition boundaries, the filegroup is used to store the data, the number of rows in each partition. Provided query is much faster as it deals with catalong views.

You can use this query to find out the number of rows in table if you have billions of rows instead firing query on actual table which may take long time to execute.

You can just replace '<ObjectName>' with the object you interested in filer criteria.





      i.name AS IndexName,


      fg.name AS FileGroupName,


      ps.name AS SchemaName,

      f.name FunctionName

FROM sys.partitions p

JOIN sys.indexes i

      ON (p.object_id = i.object_id

          AND p.index_id = i.index_id)

JOIN sys.partition_schemes ps

      ON (ps.data_space_id = i.data_space_id)

JOIN sys.partition_functions f

      ON (f.function_id = ps.function_id)

LEFT JOIN sys.partition_range_values rv   

      ON (f.function_id = rv.function_id

          AND p.partition_number = rv.boundary_id)

JOIN sys.destination_data_spaces dds

      ON (dds.partition_scheme_id = ps.data_space_id

          AND dds.destination_id = p.partition_number)

JOIN sys.filegroups fg

      ON (dds.data_space_id = fg.data_space_id)

WHERE i.index_id < 2

      AND i.object_id = Object_Id('<ObjectName>')

Tags: ,

SQL Server Scripts

Tag cloud