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.


1
0
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

Description

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
) ON [PRIMARY]

GO

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

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


-- Create a Column Store Index on the Table
Create NonClustered ColumnStore Index IDX_ColumnStore on dbo.TableWithColumnStoreIndex
(	[SalesOrderID],
	[SalesOrderDetailID],
	[CarrierTrackingNumber],
	[OrderQty],
	[ProductID],
	[SpecialOfferID],
	[UnitPrice],
	[UnitPriceDiscount],
	[LineTotal],
	[ModifiedDate]
)

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
Hi,

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.

Regards,
Richard Tkachuk
Program Manager, SQL Server