Skip to content

sys-column-store-segments-transact-sql.md

Latest commit

 

History

History
89 lines (73 loc) · 7.7 KB

sys-column-store-segments-transact-sql.md

File metadata and controls

89 lines (73 loc) · 7.7 KB
title sys.column_store_segments (Transact-SQL)
description sys.column_store_segments returns one row for each column segment in a columnstore index. There is one column segment per column per rowgroup.
author rwestMSFT
ms.author randolphwest
ms.date 1/12/2026
ms.service sql
ms.subservice system-objects
ms.topic reference
f1_keywords
column_store_segments
sys.column_store_segments_TSQL
sys.column_store_segments
column_store_segments_TSQL
helpviewer_keywords
sys.column_store_segments catalog view
dev_langs
TSQL

sys.column_store_segments (Transact-SQL)

[!INCLUDE sql-asdb-asdbmi-fabricsqldb]

Returns one row for each column segment in a columnstore index. There is one column segment per column per rowgroup. For example, a columnstore index with 10 rowgroups and 34 columns has 340 rows in this view.

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 rowgroup. 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>.
version int Version of the column segment format.
encoding_type int Type of encoding used for that segment:

1 = VALUE_BASED - non-string/binary with no dictionary (similar to 4 with some internal variations)

2 = VALUE_HASH_BASED - non-string/binary column with common values in dictionary

3 = STRING_HASH_BASED - string/binary column with common values in dictionary

4 = STORE_BY_VALUE_BASED - non-string/binary with no dictionary

5 = STRING_STORE_BY_VALUE_BASED - string/binary with no dictionary

For more information, see the Remarks section.
row_count int Number of rows in the row group.
has_nulls int 1 if the column segment has NULLs.
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 A value of 0 represents the global dictionary. A value of -1 indicates that there is no global dictionary created for this column.
secondary_dictionary_id int A non-zero value points to the local dictionary for this column in the current segment (for example, the rowgroup). A value of -1 indicates that there is no local dictionary for this segment.
min_data_id bigint For internal use only.
max_data_id bigint For internal use only.
null_value bigint Value used to represent nulls.
on_disk_size bigint Size of segment in bytes.
collation_id int Current collation when the segment was created. Maps to an internal ID. For internal use only.

Applies to: [!INCLUDEsssql22-md] and later versions, [!INCLUDE ssazure-sqldb], and [!INCLUDEssazuremi-md]
min_deep_data varbinary(18) Used for segment elimination.1 For internal use only.

Applies to: [!INCLUDEsssql22-md] and later versions, [!INCLUDE ssazure-sqldb], and [!INCLUDEssazuremi-md]
max_deep_data varbinary(18) Used for segment elimination.1 For internal use only.

Applies to: [!INCLUDEsssql22-md] and later versions, [!INCLUDE ssazure-sqldb], and [!INCLUDEssazuremi-md]

1 After upgrading to a version of the [!INCLUDE ssde-md] that supports min/max segment elimination for an expanded set of data types (introduced in [!INCLUDEsssql22-md]), the min_deep_data and max_deep_data are NULL until the columnstore index is rebuilt using an ALTER INDEX ... REBUILD or CREATE INDEX ... WITH (DROP_EXISTING = ON) statement.

Remarks

The columnstore segment encoding type is selected by the [!INCLUDEssDE-md] by analyzing the segment data with the goal of achieving the lowest storage cost. If data is mostly distinct, the [!INCLUDEssDE-md] uses value-based encoding. If data is mostly not distinct, the [!INCLUDEssDE-md] uses hash-based encoding. The choice between string-based and value-based encoding is related to the type of data being stored, whether string data or binary data. All encodings take advantage of bit-packing and run-length encoding when possible.

Columnstore segment elimination applies to numeric, date, and time data types, and the datetimeoffset data type with scale less than or equal to two. Beginning in [!INCLUDEsssql22-md], segment elimination capabilities expand to string and binary data types, the uniqueidentifier data type, and the datetimeoffset data type for scale greater than two. Segment elimination does not apply to LOB data types such as varchar(max), nvarchar(max), and varbinary(max). For more information, see What's new in columnstore indexes.

Permissions

The VIEW DEFINITION permission on the view is required. The following columns return NULL unless the user also has the SELECT permission: has_nulls, base_id, magnitude, min_data_id, max_data_id, and null_value.

[!INCLUDEssCatViewPerm] For more information, see Metadata Visibility Configuration.

Examples

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 = 5 OR i.type = 6
GROUP BY i.name, p.object_id, p.index_id, i.type_desc;

Related content