sys.column_store_segments (Transact-SQL)sys.column_store_segments (Transact-SQL)

适用对象: yesSQL ServeryesAzure SQL 数据库noAzure SQL 数据仓库no并行数据仓库APPLIES TO: yesSQL Server noAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

列存储索引中返回的每个列段的一行。Returns one row for each column segment in a columnstore index. 没有每个列每个行组的一个列段。There is one column segment per column per rowgroup. 例如,具有 10 行组和 34 列的表返回 340 行。For example, a table with 10 rowgroups and 34 columns returns 340 rows.

列名Column name 数据类型Data type 描述Description
partition_idpartition_id bigintbigint 指示分区 ID。Indicates the partition ID. 是在数据库中唯一。Is unique within a database.
hobt_idhobt_id bigintbigint 具有此 columnstore 索引的表的堆或 B 树 (hobt) 的 ID。ID of the heap or B-tree index (hobt) for the table that has this columnstore index.
column_idcolumn_id intint 列存储列的 ID。ID of the columnstore column.
segment_idsegment_id intint 行组的 ID。ID of the rowgroup. 对于向后兼容性,列名称会继续调用 segment_id 即使这是行组 id。For backward compatibility, the column name continues to be called segment_id even though this is the rowgroup ID. 可唯一地标识段使用<hobt_id,partition_id,column_id >,< segment_id >。You can uniquely identify a segment using <hobt_id, partition_id, column_id>, <segment_id>.
versionversion intint 列段格式的版本。Version of the column segment format.
encoding_typeencoding_type intint 使用该时间段的编码类型:Type of encoding used for that segment:

1 = VALUE_BASED-非字符串/二进制与没有字典 (非常类似于具有某些内部变体 4)1 = VALUE_BASED - non-string/binary with no dictionary (very similar to 4 with some internal variations)

2 = VALUE_HASH_BASED-包含字典中的常见值字符串/二进制列2 = VALUE_HASH_BASED - non-string/binary column with common values in dictionary

3 = STRING_HASH_BASED-与字典中的常见值字符串/二进制列3 = STRING_HASH_BASED - string/binary column with common values in dictionary

4 = STORE_BY_VALUE_BASED-非字符串/二进制与没有字典4 = STORE_BY_VALUE_BASED - non-string/binary with no dictionary

5 = STRING_STORE_BY_VALUE_BASED-带有没有字典字符串/二进制文件5 = STRING_STORE_BY_VALUE_BASED - string/binary with no dictionary

所有编码都充分利用位打包和运行长度编码在可能的情况。All encodings take advantage of bit-packing and run-length encoding when possible.
row_countrow_count intint 行组中的行数。Number of rows in the row group.
has_nullshas_nulls intint 1 如果列段具有 Null 值。1 if the column segment has null values.
base_idbase_id bigintbigint 如果使用编码类型 1 的基值 id。Base value id if encoding type 1 is being used. 如果未正在使用的编码类型 1,base_id 设置为-1。If encoding type 1 is not being used, base_id is set to -1.
magnitudemagnitude floatfloat 如果使用的编码类型 1 的量值。Magnitude if encoding type 1 is being used. 如果编码类型 1 未使用,则将量值设置为-1。If encoding type 1 is not being used, magnitude is set to -1.
primary_dictionary_idprimary_dictionary_id intint 值为 0 表示全局字典。A value of 0 represents the global dictionary. 值为-1 指示没有为该列创建没有全局字典。A value of -1 indicates that there is no global dictionary created for this column.
secondary_dictionary_idsecondary_dictionary_id intint 一个非零值将指向此列将在当前段 (即行组) 的本地字典。A non-zero value points to the local dictionary for this column in the current segment (i.e. the rowgroup). 值为-1 指示存在此段没有本地字典。A value of -1 indicates that there is no local dictionary for this segment.
min_data_idmin_data_id bigintbigint 列段中的最小数据 ID。Minimum data id in the column segment.
max_data_idmax_data_id bigintbigint 列段中的最大数据 ID。Maximum data id in the column segment.
null_valuenull_value bigintbigint 用于表示 Null 的值。Value used to represent nulls.
on_disk_sizeon_disk_size bigintbigint 段大小(字节)。Size of segment in bytes.


以下查询返回有关列存储索引各段的信息。The following query returns information about segments of a columnstore index.

SELECT, 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, p.object_id, p.index_id, i.type_desc ;  


所有列都要求至少VIEW DEFINITION表的权限。All columns require at least VIEW DEFINITION permission on the table. 以下各列返回 null,除非用户也具有选择权限: has_nulls、 base_id、 magnitude、 min_data_id、 max_data_id 和 null_value。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. 有关详细信息,请参阅 Metadata Visibility ConfigurationFor more information, see Metadata Visibility Configuration.

请参阅See Also

对象目录视图 (Transact-SQL) Object Catalog Views (Transact-SQL)
目录视图 (Transact-SQL) Catalog Views (Transact-SQL)
查询 SQL Server 系统目录常见问题 Querying the SQL Server System Catalog FAQ
sys.columns (Transact-SQL) sys.columns (Transact-SQL)
sys.all_columns (TRANSACT-SQL) sys.all_columns (Transact-SQL)
sys.computed_columns (TRANSACT-SQL) sys.computed_columns (Transact-SQL)
列存储索引指南 Columnstore Indexes Guide
sys.column_store_dictionaries (Transact-SQL)sys.column_store_dictionaries (Transact-SQL)