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

Status : 

  By Design<br /><br />
		The product team believes this item works according to its intended design.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.

Sign in
to vote
ID 788628 Comments
Status Closed Workarounds
Type Bug Repros 0
Opened 5/26/2013 5:11:26 AM
Access Restriction Public


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 as index_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