sys.column_store_dictionaries (Transact-SQL)

 

Updated: June 10, 2016

THIS TOPIC APPLIES TO: yesSQL Server (starting with 2012)noAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

Contains a row for each dictionary used in xVelocity memory optimized columnstore indexes. Dictionaries are used to encode some, but not all data types, therefore not all columns in a columnstore index have dictionaries. A dictionary can exist as a primary dictionary (for all segments) and possibly for other secondary dictionaries used for a subset of the column's segments.

Column nameData typeDescription
hobt_idbigintID of the heap or B-tree index (hobt) for the table that has this columnstore index.
column_idintID of the columnstore column starting with 1. The first column has ID = 1, the second column has ID = 2, etc.
dictionary_idintThere can be two kinds of dictionaries, global and local, associated with a column segment. A dictionary_id of 0 represents the global dictionary that is shared across all column segments (one for each row group) for that column.
versionintVersion of the dictionary format.
typeintDictionary type:

1 – Hash dictionary containing int values

2 – Not used

3 – Hash dictionary containing string values

4 – Hash dictionary containing float values

For more information about dictionaries, see Columnstore Indexes Guide.
last_idintThe last data ID in the dictionary.
entry_countbigintNumber of entries in the dictionary.
on_disc_sizebigintSize of dictionary in bytes.
partition_idbigintIndicates the partition ID. Is unique within a database.

All columns require at least VIEW DEFINITION permission on the table. The following columns return null unless the user also has SELECT permission: last_id, entry_count, data_ptr.

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.

Object Catalog Views (Transact-SQL)
Catalog Views (Transact-SQL)
Querying the SQL Server System Catalog FAQ
sys.columns (Transact-SQL)
sys.all_columns (Transact-SQL)
sys.computed_columns (Transact-SQL)
Columnstore Indexes Guide
Columnstore Indexes Guide
sys.column_store_segments (Transact-SQL)

Community Additions

ADD
Show: