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)

Catalog Views (Transact-SQL)

sys.columns (Transact-SQL)

sys.all_columns (Transact-SQL)

sys.computed_columns (Transact-SQL)

sys.column_store_dictionaries (Transact-SQL)

Concepts

Querying the SQL Server System Catalog FAQ

Columnstore Indexes

Columnstore Indexes