Export (0) Print
Expand All
Expand Minimize

sys.column_store_segments (Transact-SQL)

Contains a row for each column in a columnstore index.

Applies to: SQL Server (SQL Server 2012 through current version).

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.

Community Additions

ADD
Show:
© 2014 Microsoft