Skip to content

sys-pdw-nodes-partitions-transact-sql.md

Latest commit

 

History

History
75 lines (60 loc) · 3.52 KB

sys-pdw-nodes-partitions-transact-sql.md

File metadata and controls

75 lines (60 loc) · 3.52 KB
title sys.pdw_nodes_partitions (Transact-SQL) | Microsoft Docs
ms.custom
ms.date 03/03/2017
ms.prod sql-non-specified
ms.reviewer
ms.service sql-data-warehouse
ms.suite
ms.technology
database-engine
ms.tgt_pltfrm
ms.topic language-reference
dev_langs
TSQL
ms.assetid b4216752-4813-4b2c-b259-7d8ffc6cc190
caps.latest.revision 11
author barbkess
ms.author barbkess
manager jhubbard

sys.pdw_nodes_partitions (Transact-SQL)

[!INCLUDEtsql-appliesto-xxxxxx-xxxx-asdw-pdw_md]

Contains a row for each partition of all the tables, and most types of indexes in a [!INCLUDEssSDW] database. All tables and indexes contain at least one partition, whether or not they are explicitly partitioned.

Column name Data type Description
partition_id bigint id of the partition. Is unique within a database.
object_id int id of the object to which this partition belongs. Every table or view is composed of at least one partition.
index_id int id of the index within the object to which this partition belongs.
partition_number int 1-based partition number within the owning index or heap. For [!INCLUDEssSDW], the value of this column is 1.
hobt_id bigint ID of the data heap or B-tree that contains the rows for this partition.
rows bigint Approximate number of rows in this partition.
data_compression int Indicates the state of compression for each partition:

0 = NONE

1 = ROW

2 = PAGE

3 = COLUMNSTORE
data_compression_desc nvarchar(60) Indicates the state of compression for each partition. Possible values are NONE, ROW, and PAGE.
pdw_node_id int Unique identifier of a [!INCLUDEssSDW] node.

Permissions

Requires CONTROL SERVER permission.

Examples: [!INCLUDEssSDWfull] and [!INCLUDEssPDW]

Example A: Display rows in each partition within each distribution

Applies to: [!INCLUDEssSDW], [!INCLUDEssPDW]

To display the number of rows in each partition within each distribution, use DBCC PDW_SHOWPARTITIONSTATS (SQL Server PDW) .

Example B: Uses system views to view rows in each partition of each distribution of a table

Applies to: [!INCLUDEssSDW]

This query returns the number of rows in each partition of each distribution of the table myTable.

SELECT o.name, pnp.index_id, pnp.partition_id, pnp.rows,   
    pnp.data_compression_desc, pnp.pdw_node_id  
FROM sys.pdw_nodes_partitions AS pnp  
JOIN sys.pdw_nodes_tables AS NTables  
    ON pnp.object_id = NTables.object_id  
AND pnp.pdw_node_id = NTables.pdw_node_id  
JOIN sys.pdw_table_mappings AS TMap  
    ON NTables.name = TMap.physical_name 
    AND substring(TMap.physical_name,40, 10) = pnp.distribution_id 
JOIN sys.objects AS o  
    ON TMap.object_id = o.object_id  
WHERE o.name = 'myTable'  
ORDER BY o.name, pnp.index_id, pnp.partition_id;

See Also

SQL Data Warehouse and Parallel Data Warehouse Catalog Views