Export (0) Print
Expand All
Expand Minimize

sys.column_store_segments (Transact-SQL)

SQL Server 2012

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.

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

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.

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft