sys.column_store_segments (Transact-SQL)

Članak
03/14/2022
2 min. za čitanje

Applies to: SQL Server (all supported versions)

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

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 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	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 (i.e. the rowgroup). A value of -1 indicates that there is no local dictionary for this segment.
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 columnstore segment encoding type is selected by the Database Engine with the goal of achieving the lowest storage cost, by analyzing the segment data. If data is mostly distinct, the Database Engine uses value-based encoding. If data is mostly not distinct, the Database Engine 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.

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.

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 ;  
GO

sys.column_store_segments (Transact-SQL)

Je li ova stranica od pomoći?

Remarks

Permissions

Examples

The following query returns information about segments of a columnstore index.

See Also