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?


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'
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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.