Skip to content

Commit 0d26729

Browse files
GitHubber17GitHubber17
authored
Merge pull request #5328 from hirokib/master
updates to incorrect docs
2 parents 4e024a4 + cc778e4 commit 0d26729

1 file changed

Lines changed: 61 additions & 59 deletions

File tree

‎docs/relational-databases/system-catalog-views/sys-pdw-nodes-column-store-segments-transact-sql.md‎

Lines changed: 61 additions & 59 deletions
Original file line numberDiff line numberDiff line change
@@ -1,7 +1,7 @@
11
---
22
title: "sys.pdw_nodes_column_store_segments (Transact-SQL) | Microsoft Docs"
33
ms.custom: ""
4-
ms.date: "03/03/2017"
4+
ms.date: "03/28/2018"
55
ms.prod: ""
66
ms.prod_service: "sql-data-warehouse, pdw"
77
ms.service: "sql-data-warehouse"
@@ -16,76 +16,78 @@ dev_langs:
1616
- "TSQL"
1717
ms.assetid: e2fdf8e9-1b74-4682-b2d4-c62aca053d7f
1818
caps.latest.revision: 9
19-
author: "barbkess"
20-
ms.author: "barbkess"
21-
manager: "craigg"
19+
author: "hirokib"
20+
ms.author: "elbutter;barbkess"
21+
manager: "jrj"
22+
2223
ms.workload: "Inactive"
2324
---
2425
# sys.pdw_nodes_column_store_segments (Transact-SQL)
2526
[!INCLUDE[tsql-appliesto-xxxxxx-xxxx-asdw-pdw-md](../../includes/tsql-appliesto-xxxxxx-xxxx-asdw-pdw-md.md)]
2627

27-
Contains a row for each column in a columnstore index.
28-
29-
|Column name|Data type|Description|
30-
|-----------------|---------------|-----------------|
31-
|**partition_id**|**bigint**|Indicates the partition ID. Is unique within a database.|
32-
|**hobt_id**|**bigint**|ID of the heap or B-tree index (hobt) for the table that has this columnstore index.|
33-
|**column_id**|**int**|ID of the columnstore column.|
34-
|**segment_id**|**int**|ID of the column segment.|
35-
|**version**|**int**|Version of the column segment format.|
36-
|**encoding_type**|**int**|Type of encoding used for that segment.|
37-
|**row_count**|**int**|Number of rows in the row group.|
38-
|**has_nulls**|**int**|1 if the column segment has null values.|
39-
|**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.|
40-
|**magnitude**|**float**|Magnitude if encoding type 1 is being used. If encoding type 1 is not being used, magnitude is set to 1.|
41-
|**primary__dictionary_id**|**int**|Id of primary dictionary.|
42-
|**secondary_dictionary_id**|**int**|Id of secondary dictionary. Returns -1 if there is no secondary dictionary.|
43-
|**min_data_id**|**bigint**|Minimum data id in the column segment.|
44-
|**max_data_id**|**bigint**|Maximum data id in the column segment.|
45-
|**null_value**|**bigint**|Value used to represent nulls.|
46-
|**on_disk_size**|**bigint**|Size of segment in bytes.|
47-
|**pdw_node_id**|**int**|Unique identifier of a [!INCLUDE[ssSDW](../../includes/sssdw-md.md)] note.|
48-
28+
Contains a row for each column in a columnstore index.
29+
30+
| Column name | Data type | Description |
31+
| --------------------------- | ---------- | ------------------------------------------------------------ |
32+
| **partition_id** | **bigint** | Indicates the partition ID. Is unique within a database. |
33+
| **hobt_id** | **bigint** | ID of the heap or B-tree index (hobt) for the table that has this columnstore index. |
34+
| **column_id** | **int** | ID of the columnstore column. |
35+
| **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>. |
36+
| **version** | **int** | Version of the column segment format. |
37+
| **encoding_type** | **int** | Type of encoding used for that segment:<br /><br /> 1 = VALUE_BASED - non-string/binary with no dictionary (similar to 4 with some internal variations)<br /><br /> 2 = VALUE_HASH_BASED - non-string/binary column with common values in dictionary<br /><br /> 3 = STRING_HASH_BASED - string/binary column with common values in dictionary<br /><br /> 4 = STORE_BY_VALUE_BASED - non-string/binary with no dictionary<br /><br /> 5 = STRING_STORE_BY_VALUE_BASED - string/binary with no dictionary<br /><br /> All encodings take advantage of bit-packing and run-length encoding when possible. |
38+
| **row_count** | **int** | Number of rows in the row group. |
39+
| **has_nulls** | **int** | 1 if the column segment has null values. |
40+
| **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. |
41+
| **magnitude** | **float** | Magnitude if encoding type 1 is being used. If encoding type 1 is not being used, magnitude is set to 1. |
42+
| **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. |
43+
| **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. |
44+
| **min_data_id** | **bigint** | Minimum data ID in the column segment. |
45+
| **max_data_id** | **bigint** | Maximum data ID in the column segment. |
46+
| **null_value** | **bigint** | Value used to represent nulls. |
47+
| **on_disk_size** | **bigint** | Size of segment in bytes. |
48+
| **pdw_node_id** | **int** | Unique identifier of a [!INCLUDE[ssSDW](../../includes/sssdw-md.md)] node. |
49+
4950
## Examples: [!INCLUDE[ssSDWfull](../../includes/sssdwfull-md.md)] and [!INCLUDE[ssPDW](../../includes/sspdw-md.md)]
50-
The following query returns information about segments of a columnstore index.
51-
52-
```sql
53-
SELECT i.name, p.object_id, p.index_id, i.type_desc,
54-
COUNT(*) AS number_of_segments
55-
FROM sys.column_store_segments AS s
56-
INNER JOIN sys.partitions AS p
57-
ON s.hobt_id = p.hobt_id
58-
INNER JOIN sys.indexes AS i
59-
ON p.object_id = i.object_id
60-
WHERE i.type = 6
61-
GROUP BY i.name, p.object_id, p.index_id, i.type_desc ;
62-
```
63-
64-
Join sys.pdw_nodes_column_store_segments with other system tables to determine the row count and on-disk size of the segments.
65-
66-
```
67-
SELECT o.name, css.hobt_id, css. column_id, css.pdw_node_id, css.row_count, css.on_disk_size
68-
FROM sys.pdw_nodes_column_store_segments AS css
69-
JOIN sys.pdw_nodes_partitions AS pnp
70-
ON css.partition_id = pnp.partition_id
71-
JOIN sys.pdw_nodes_tables AS part
72-
ON pnp.object_id = part.object_id
73-
AND pnp.pdw_node_id = part.pdw_node_id
74-
JOIN sys.pdw_table_mappings AS TMap
75-
ON part.name = TMap.physical_name
76-
JOIN sys.objects AS o
77-
ON TMap.object_id = o.object_id
78-
ORDER BY css.hobt_id, css.column_id;
79-
```
80-
51+
52+
Join sys.pdw_nodes_column_store_segments with other system tables to determine the number of columnstore segments per logical table.
53+
54+
```sql
55+
SELECT sm.name as schema_nm
56+
, tb.name as table_nm
57+
, nc.name as col_nm
58+
, nc.column_id
59+
, COUNT(*) as segment_count
60+
FROM sys.[schemas] sm
61+
JOIN sys.[tables] tb ON sm.[schema_id] = tb.[schema_id]
62+
JOIN sys.[pdw_table_mappings] mp ON tb.[object_id] = mp.[object_id]
63+
JOIN sys.[pdw_nodes_tables] nt ON nt.[name] = mp.[physical_name]
64+
JOIN sys.[pdw_nodes_partitions] np ON np.[object_id] = nt.[object_id]
65+
AND np.[pdw_node_id] = nt.[pdw_node_id]
66+
AND np.[distribution_id] = nt.[distribution_id]
67+
JOIN sys.[pdw_nodes_columns] nc ON np.[object_id] = nc.[object_id]
68+
AND np.[pdw_node_id] = nc.[pdw_node_id]
69+
AND np.[distribution_id] = nc.[distribution_id]
70+
JOIN sys.[pdw_nodes_column_store_segments] rg ON rg.[partition_id] = np.[partition_id]
71+
AND rg.[pdw_node_id] = np.[pdw_node_id]
72+
AND rg.[distribution_id] = np.[distribution_id]
73+
AND rg.[column_id] = nc.[column_id]
74+
GROUP BY sm.name
75+
, tb.name
76+
, nc.name
77+
, nc.column_id
78+
ORDER BY table_nm
79+
, nc.column_id
80+
, sm.name
81+
```
82+
8183
## Permissions
8284
Requires **VIEW SERVER STATE** permission.
83-
85+
8486
## See Also
8587
[SQL Data Warehouse and Parallel Data Warehouse Catalog Views](../../relational-databases/system-catalog-views/sql-data-warehouse-and-parallel-data-warehouse-catalog-views.md)
8688
[CREATE COLUMNSTORE INDEX &#40;Transact-SQL&#41;](../../t-sql/statements/create-columnstore-index-transact-sql.md)
8789
[sys.pdw_nodes_column_store_row_groups &#40;Transact-SQL&#41;](../../relational-databases/system-catalog-views/sys-pdw-nodes-column-store-row-groups-transact-sql.md)
8890
[sys.pdw_nodes_column_store_dictionaries &#40;Transact-SQL&#41;](../../relational-databases/system-catalog-views/sys-pdw-nodes-column-store-dictionaries-transact-sql.md)
89-
91+
9092

9193

0 commit comments

Comments
 (0)