Sql-server – SQL Server – Show Content of a partition

partitioningsql server

I did somes partitions on my sql database with the wizard of microsoft Sql Server Management Studio. But i wanted to know if it's possible to do an request for show just lines contains by one partition.

I tried this : 

select * from  MyTable where $PARTITION.LinkTest(ID) = 2;

But it's just show me the result of the partition function LinkTest not the lines contained on one filegroups or file. Someone know how I can do that?

Best Answer

This is a pretty basic query I use to get rows ( lines? ) per object on my partitioned schemes:

SELECT  DISTINCT ObjectName = so.name, ds.name, 
        sp.partition_number, sp.rows
FROM    sys.objects so
INNER JOIN sys.partitions sp
    ON  so.object_id = sp.object_id
INNER JOIN sys.destination_data_spaces dds
    ON  sp.partition_number = dds.destination_id
INNER JOIN sys.data_spaces ds
    ON  dds.data_space_id = ds.data_space_id
WHERE   so.is_ms_shipped = 0
    AND sp.rows > 0
    AND ds.name <> 'PRIMARY'
ORDER BY so.name, ds.name;

You could add the name of your table and the partition you're interested in to the predicate:

    AND so.name = 'MyTable'
    AND sp.partition_number = 2

For a little more detailed information ( per index, compression stats, etc ), I use this little guy:

SELECT  DatabaseName = DB_NAME(), SchemaName = ss.name, 
        TableName = OBJECT_NAME( so.object_id ),
        IndexID = p.index_id, IndexName = CASE
            WHEN p.index_id = 0 
              THEN 'HEAP'
            ELSE i.name
        END, [Compression] = sp.data_compression_desc, PartitionNumber = p.partition_number,
        LowerBoundary = prv_left.value, UpperBoundary = prv_right.value,
        FileGroupName = CASE
            WHEN fg.name IS NULL 
              THEN ds.name
            ELSE fg.name
        END, UsedPagesInMB = CONVERT( NUMERIC( 18, 2 ), p.used_page_count * 0.0078125 ),
        DataPagesInMB = CONVERT( NUMERIC( 18, 2 ), p.in_row_data_page_count * 0.0078125 ),
        ReservedPagesInMB = CONVERT( NUMERIC( 18, 2 ), p.reserved_page_count * 0.0078125 ),
        [RowCount] = CASE
            WHEN p.index_id IN ( 0, 1 )
              THEN p.row_count
            ELSE 0
        END, [Type] = CASE
            WHEN p.index_id IN ( 0, 1 ) 
              THEN 'DATA'
            ELSE 'INDEX'
        END
FROM sys.dm_db_partition_stats p
INNER JOIN sys.objects so
    ON  p.object_id = so.object_id
INNER JOIN sys.partitions sp
    ON  so.object_id = sp.object_id
    AND p.partition_id = sp.partition_id
    AND p.partition_number = sp.partition_number
INNER JOIN sys.schemas ss
    ON  so.schema_id = ss.schema_id
INNER JOIN sys.indexes i
    ON  i.object_id = p.object_id
    AND i.index_id = p.index_id
INNER JOIN sys.data_spaces ds
    ON  ds.data_space_id = i.data_space_id
LEFT OUTER JOIN sys.partition_schemes ps
    ON  ps.data_space_id = i.data_space_id
LEFT OUTER JOIN sys.destination_data_spaces dds
    ON  dds.partition_scheme_id = ps.data_space_id
    AND dds.destination_id = p.partition_number
LEFT OUTER JOIN sys.filegroups fg
    ON  fg.data_space_id = dds.data_space_id
LEFT OUTER JOIN sys.partition_range_values prv_right
    ON  prv_right.function_id = ps.function_id
    AND prv_right.boundary_id = p.partition_number
LEFT OUTER JOIN sys.partition_range_values prv_left
    ON  prv_left.function_id = ps.function_id
    AND prv_left.boundary_id = p.partition_number - 1
WHERE   OBJECTPROPERTY( so.object_id, 'ISMSSHipped' ) = 0;
Related Question