Home Dashboard Directory Help

select * from sys.column_store_dictionaries does not show all columnstore columns by nl19217


 as By Design Help for as By Design

Sign in
to vote
Type: Bug
ID: 788628
Opened: 5/26/2013 5:11:26 AM
Access Restriction: Public
User(s) can reproduce this bug


When I run the query select * from sys.column_store_dictionaries to get the information from my column_store index the result does not show all included columns.
see this demo

CREATE TABLE [dbo].[TableWithColumnStoreIndex](
    [SalesOrderID] [int] NOT NULL,
    [SalesOrderDetailID] [int] IDENTITY(1,1) NOT NULL,
    [CarrierTrackingNumber] [nvarchar](25) NULL,
    [OrderQty] [smallint] NOT NULL,
    [ProductID] [int] NOT NULL,
    [SpecialOfferID] [int] NOT NULL,
    [UnitPrice] [money] NOT NULL,
    [UnitPriceDiscount] [money] NOT NULL,
    [LineTotal] [numeric](18, 6) NULL,
    [rowguid] [uniqueidentifier] NOT NULL,
    [ModifiedDate] [datetime] NOT NULL


Alter Table dbo.TableWithColumnStoreIndex Alter Column LineTotal numeric(18,6)

insert into dbo.TableWithColumnStoreIndex
[UnitPriceDiscount],[LineTotal],rowguid, [ModifiedDate])
    select [SalesOrderID],[CarrierTrackingNumber],[OrderQty],[ProductID],[SpecialOfferID],[UnitPrice],
[UnitPriceDiscount],[LineTotal],rowguid, [ModifiedDate] from Sales.SalesOrderDetail

-- Create a Column Store Index on the Table
Create NonClustered ColumnStore Index IDX_ColumnStore on dbo.TableWithColumnStoreIndex
(    [SalesOrderID],

the following query

select * from sys.column_store_dictionaries

gives 8 columns back while the columnstore index is created over 10 columns
or more clearly with the query below (with column names)

SELECT i.name as index_name, c.name as column_name, cs.column_id, cs.on_disk_size, p.rows
FROM sys.column_store_dictionaries cs
INNER JOIN sys.partitions p on p.partition_id = cs.partition_id
INNER JOIN sys.indexes i on i.object_id = p.object_id and i.index_id = p.index_id and i.type = 6
INNER JOIN sys.columns c on cs.column_id = c.column_id and c.object_id = p.object_id
WHERE p.object_id = object_id('dbo.TableWithColumnStoreIndex')

When I change the column store index to 6 or 5 columns the query select * from sys.column_store_dictionaries also gives back less columns than there are in the definition.

Sign in to post a comment.
Posted by Microsoft on 7/17/2013 at 11:00 AM

Dictionairies are used to encode some, but not all data types. Not all columns use dictionaries.

Documentation is confusing (well, wrong) that this table contains a row for each column in a columnstore index. It actually contains at one row for each column that uses a dictionary for the primary dictionary (all segments) and possibly others for secondary dictionaries for a subset of the column's segments. In other words, there isn't a relationship between the number of columns and the number of dictionaries in column_store_dictionaries.

Richard Tkachuk
Program Manager, SQL Server
Sign in to post a workaround.