Note
Access to this page requires authorization. You can try signing in or .
Access to this page requires authorization. You can try .
sys.pdw_nodes_column_store_segments (Transact-SQL)
Applies to: 👁 Image
Azure Synapse Analytics 👁 Image
Analytics Platform System (PDW)
Contains a row for each column in a columnstore index.
| Column name | Data type | Description |
|---|---|---|
| partition_id | bigint | Indicates the partition ID. Is unique within a database. |
| hobt_id | bigint | ID of the heap or B-tree index (hobt) for the table that has this columnstore index. |
| column_id | int | ID of the columnstore column. |
| segment_id | int | ID of the column segment. For backward compatibility, the column name continues to be called segment_id even though this is the rowgroup ID. You can uniquely identify a segment using <hobt_id, partition_id, column_id>, <segment_id>. |
| version | int | Version of the column segment format. |
| encoding_type | int | Type of encoding used for that segment: 1 = VALUE_BASED - non-string/binary with no dictionary (similar to 4 with some internal variations) 2 = VALUE_HASH_BASED - non-string/binary column with common values in dictionary 3 = STRING_HASH_BASED - string/binary column with common values in dictionary 4 = STORE_BY_VALUE_BASED - non-string/binary with no dictionary 5 = STRING_STORE_BY_VALUE_BASED - string/binary with no dictionary All encodings take advantage of bit-packing and run-length encoding when possible. |
| row_count | int | Number of rows in the row group. |
| has_nulls | int | 1 if the column segment has null values. |
| base_id | bigint | Base value ID if encoding type 1 is being used. If encoding type 1 is not being used, base_id is set to 1. |
| magnitude | float | Magnitude if encoding type 1 is being used. If encoding type 1 is not being used, magnitude is set to 1. |
| primary__dictionary_id | int | ID of primary dictionary. A non-zero value points to the local dictionary for this column in the current segment (i.e. the rowgroup). A value of -1 indicates that there is no local dictionary for this segment. |
| secondary_dictionary_id | int | ID of secondary dictionary. A non-zero value points to the local dictionary for this column in the current segment (i.e. the rowgroup). A value of -1 indicates that there is no local dictionary for this segment. |
| min_data_id | bigint | Minimum data ID in the column segment. |
| max_data_id | bigint | Maximum data ID in the column segment. |
| null_value | bigint | Value used to represent nulls. |
| on_disk_size | bigint | Size of segment in bytes. |
| pdw_node_id | int | Unique identifier of a Azure Synapse Analytics node. |
Examples: Azure Synapse Analytics and Analytics Platform System (PDW)
Join sys.pdw_nodes_column_store_segments with other system tables to determine the number of columnstore segments per logical table.
SELECT sm.name as schema_nm
, tb.name as table_nm
, nc.name as col_nm
, nc.column_id
, COUNT(*) as segment_count
FROM sys.[schemas] sm
JOIN sys.[tables] tb ON sm.[schema_id] = tb.[schema_id]
JOIN sys.[pdw_table_mappings] mp ON tb.[object_id] = mp.[object_id]
JOIN sys.[pdw_nodes_tables] nt ON nt.[name] = mp.[physical_name]
JOIN sys.[pdw_nodes_partitions] np ON np.[object_id] = nt.[object_id]
AND np.[pdw_node_id] = nt.[pdw_node_id]
AND np.[distribution_id] = nt.[distribution_id]
JOIN sys.[pdw_nodes_columns] nc ON np.[object_id] = nc.[object_id]
AND np.[pdw_node_id] = nc.[pdw_node_id]
AND np.[distribution_id] = nc.[distribution_id]
JOIN sys.[pdw_nodes_column_store_segments] rg ON rg.[partition_id] = np.[partition_id]
AND rg.[pdw_node_id] = np.[pdw_node_id]
AND rg.[distribution_id] = np.[distribution_id]
AND rg.[column_id] = nc.[column_id]
GROUP BY sm.name
, tb.name
, nc.name
, nc.column_id
ORDER BY table_nm
, nc.column_id
, sm.name ;
Permissions
Requires VIEW SERVER STATE permission.
See Also
Azure Synapse Analytics and Parallel Data Warehouse Catalog Views
CREATE COLUMNSTORE INDEX (Transact-SQL)
sys.pdw_nodes_column_store_row_groups (Transact-SQL)
sys.pdw_nodes_column_store_dictionaries (Transact-SQL)
Feedback
Was this page helpful?