sys.column_store_segments (Transact-SQL)
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. |
version |
int |
Version of the column segment format. |
encoding_type |
int |
Type of encoding used for that segment. |
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. |
secondary_dictionary_id |
int |
Id of secondary dictionary. Returns -1 if there is no secondary dictionary. |
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. |
Remarks
The following query returns information about segments of a columnstore index.
SELECT i.name, p.object_id, p.index_id, i.type_desc,
COUNT(*) AS number_of_segments
FROM sys.column_store_segments AS s
INNER JOIN sys.partitions AS p
ON s.hobt_id = p.hobt_id
INNER JOIN sys.indexes AS i
ON p.object_id = i.object_id
WHERE i.type = 6
GROUP BY i.name, p.object_id, p.index_id, i.type_desc ;
GO
Permissions
All columns require at least VIEW DEFINITION permission on the table. The following columns return null unless the user also has SELECT permission: has_nulls, base_id, magnitude, min_data_id, max_data_id, and null_value.
The visibility of the metadata in catalog views is limited to securables that a user either owns or on which the user has been granted some permission. For more information, see Metadata Visibility Configuration.
See Also
Reference
Object Catalog Views (Transact-SQL)
sys.all_columns (Transact-SQL)
sys.computed_columns (Transact-SQL)
sys.column_store_dictionaries (Transact-SQL)