MS BICOE All about data​: dig, distill, visualize, prognosticate & prescribe

There 2 new system catalog views added into SQL server 2012 for columnstore indexes:

1) sys.column_store_segments

2) sys.column_store_dictionaries

(Note: there was another view: sys.column_store_index_stats before the RTM version, but it is no long available in the final release. Not sure why this is dropped.)

These 2 system views are very useful to find out more about an columnstore index in your database, like segments, datasize etc.

For both system catalog views, the BOL states that 'Contains a row for each column in a columnstore index', which means each column in a columnstore index will have a row in these system viewss. While this statement is true, the reverse is not always. There are some times that you might notice that there is an extra column in  sys.column_store_segments view. What is this extra column? let's go to find it out.

As the original FactResellerSales table is a small table, we can use this table to perform the test for this purpose. I will create a nonclustered columnstore index directly on this table for all the columns (27 columns)

Use [AdventureWorksDW2012]
Go

If Exists (	Select *
			From sys.indexes
			Where	type = 6
					And Object_id = Object_id('dbo.FactResellerSales')
			)
	Begin
		Declare @IndexName sysname
				, @SQL nvarchar(max)

		Select	@IndexName = Name
		From	sys.indexes
		Where	type = 6
				And Object_id = Object_id('dbo.FactResellerSales')
		Set @SQL = 'DROP INDEX ' + quotename(@IndexName) + ' ON [dbo].[FactResellerSales];'
		
		Exec (@SQL);

		--DROP INDEX [idx_FactResellerSales_CI] ON [dbo].[FactResellerSales];
	End
--columns

CREATE NONCLUSTERED COLUMNSTORE INDEX [idx_FactResellerSales_CI] 
ON [dbo].[FactResellerSales]
(
	[ProductKey],
	[OrderDateKey],
	[DueDateKey],
	[ShipDateKey],
	[ResellerKey],
	[EmployeeKey],
	[PromotionKey],
	[CurrencyKey],
	[SalesTerritoryKey],
	[SalesOrderNumber],
	[SalesOrderLineNumber],
	[RevisionNumber],
	[OrderQuantity],
	[UnitPrice],
	[ExtendedAmount],
	[UnitPriceDiscountPct],
	[DiscountAmount],
	[ProductStandardCost],
	[TotalProductCost],
	[SalesAmount],
	[TaxAmt],
	[Freight],
	[CarrierTrackingNumber],
	[CustomerPONumber],
	[OrderDate],
	[DueDate],
	[ShipDate]
);
GO

Select	S.*
From	sys.column_store_segments S
			Inner Join
		sys.Partitions P
			On	S.hobt_id = P.hobt_id
			Inner Join
		sys.indexes I
			On	i.object_id = P.object_id
				And I.index_id = P.index_id
Where	I.Object_id = Object_Id('FactResellerSales')
		And I.Type = 6

From the results we can see that there is exactly 27 columns in the sys.column_store_segments table. So far so good. now let's create an exctaly same structure table from FactResellerSales without any B-Tree indexes, and create a noclustered columnstore index on it:

Use [AdventureWorksDW2012]
Go
Select	*
Into	FactResellerSales_Heap
From	[dbo].[FactResellerSales];

CREATE NONCLUSTERED COLUMNSTORE INDEX [idx_FactResellerSales_Heap_CI] 
ON [dbo].[FactResellerSales_Heap]
(
	[ProductKey],
	[OrderDateKey],
	[DueDateKey],
	[ShipDateKey],
	[ResellerKey],
	[EmployeeKey],
	[PromotionKey],
	[CurrencyKey],
	[SalesTerritoryKey],
	[SalesOrderNumber],
	[SalesOrderLineNumber],
	[RevisionNumber],
	[OrderQuantity],
	[UnitPrice],
	[ExtendedAmount],
	[UnitPriceDiscountPct],
	[DiscountAmount],
	[ProductStandardCost],
	[TotalProductCost],
	[SalesAmount],
	[TaxAmt],
	[Freight],
	[CarrierTrackingNumber],
	[CustomerPONumber],
	[OrderDate],
	[DueDate],
	[ShipDate]
);
GO

Select	S.*
From	sys.column_store_segments S
			Inner Join
		sys.Partitions P
			On	S.hobt_id = P.hobt_id
			Inner Join
		sys.indexes I
			On	i.object_id = P.object_id
				And I.index_id = P.index_id
Where	I.Object_id = Object_Id('FactResellerSales_Heap')
		And I.Type = 6

This query returns back 28 rows and the maximun column_id value of sys.column_store_segments is 28. This 28th column in the sys.column_store_segments table is an extra column than the base table. Before we try to conclude what this column is, I'm going to do another test.  I will create an exctaly same structure table from FactResellerSales and create an non-unique clustered index on the new created table, and then create a noclustered columnstore index on it:

Use [AdventureWorksDW2012]
Go
Select	*
Into	FactResellerSales_Clustered_nonunique
From	[dbo].[FactResellerSales];

Create clustered index idx_FactResellerSales_Clustered_nonunique_1 on  [dbo].[FactResellerSales_Clustered_nonunique] ([OrderDateKey]);

CREATE NONCLUSTERED COLUMNSTORE INDEX [idx_FactResellerSales_Clustered_nonunique_CI] 
ON [dbo].[FactResellerSales_Clustered_nonunique]
(
	[ProductKey],
	[OrderDateKey],
	[DueDateKey],
	[ShipDateKey],
	[ResellerKey],
	[EmployeeKey],
	[PromotionKey],
	[CurrencyKey],
	[SalesTerritoryKey],
	[SalesOrderNumber],
	[SalesOrderLineNumber],
	[RevisionNumber],
	[OrderQuantity],
	[UnitPrice],
	[ExtendedAmount],
	[UnitPriceDiscountPct],
	[DiscountAmount],
	[ProductStandardCost],
	[TotalProductCost],
	[SalesAmount],
	[TaxAmt],
	[Freight],
	[CarrierTrackingNumber],
	[CustomerPONumber],
	[OrderDate],
	[DueDate],
	[ShipDate]
);
GO

Select	S.*
From	sys.column_store_segments S
			Inner Join
		sys.Partitions P
			On	S.hobt_id = P.hobt_id
			Inner Join
		sys.indexes I
			On	i.object_id = P.object_id
				And I.index_id = P.index_id
Where	I.Object_id = Object_Id('FactResellerSales_Clustered_nonunique')
		And I.Type = 6

Again the result returns 28 rows and the 28th column in the sys.column_store_segments table is an extra column than the base table.

Now we have a clear picture:

So why the nonclustered columnstore index on a heap table will have an extra column in sys.column_store_segments?

It seems that the nonclucstered columnstore indexes like a B-Tree nonclustered index need a RID (Row ID) to refer back to the base heap table.

For a table with a none unique colustered index, a 4 byte integer uniquifier is added into the table. We can examin this in three different ways.

If you use the old sysindexes table there is a keycnt column will tell you exactly how many fields are used to form a key for the index.

Select	'FactResellerSales_Clustered_Nonunique' As Table_Name
		, Keycnt as clustered_index_keys
From	sysindexes A, sys.indexes B
Where	A.id = Object_id ('FactResellerSales_Clustered_Nonunique')
		And A.id = b.object_id
		And A.indid = B.index_id
		And	B.Type = 6

This returns 2. as we only used ([OrderDateKey]) to create the idx_FactResellerSales_Clustered_nonunique_1 clustered index, which indicate that an unique identifier is added by system. The sysindexes is an SQL server 2000 system table, which is included as a view for backward compatibility only. Although it is still available now, it is better to avoid use this and this view merely tells the count of keys with no more further information.

There is a new undocumented system view: sys.system_internals_partition_columns, which has much detailed information about columns in a table partition. From this view, I can clearly get the aditional uniquifier column is added for this nonunique clustered index. Run the script below:

Select	Object_Name(B.object_id) As Table_Name
		, partition_column_id
		, Col_Name(B.object_id, partition_column_id) As Column_Name
		, C.Name AS Data_Type
		, Key_ordinal
		, Count(Key_Ordinal) Over() As Key_Count --Total number of keys for the clustered index
		, is_uniqueifier
From	sys.system_internals_partition_columns A
			Inner Join
		sys.partitions B
			On	A.partition_id = B.partition_id
			Inner Join
		sys.types C
			On	A.system_type_id = C.system_type_id
Where	B.object_id = object_id('FactResellerSales_Clustered_nonunique')
		And B.index_id = 1		--Clustered index
		And key_ordinal != 0	--Only interested in the Clustered index key columns

From the above result, we can see that a uniquifier is added and the data type is Integer and there is no column name associated with it.

We also can use the DBCC Page to delve into the physical data page to see the uniquifier is added in the page level.

Use [AdventureWorksDW2012]
Go

Select	P.database_id
		, P.allocated_page_file_id
		, P.allocated_page_page_id
		, P.allocation_unit_type_desc
From	sys.indexes I
			Cross Apply
		sys.dm_db_database_page_allocations(DB_ID(), I.object_id, I.index_id, Null, 'limited') P
Where 
		I.object_id = Object_id ('FactResellerSales_clustered_nonunique')
		And P.is_iam_page = 0
		And P.is_allocated = 1
		And P.is_mixed_page_allocation = 0
		And I.Type = 1  --Type 0 is heap
Go

DBCC traceon(3604, -1)
Go

DBCC page (
			7,				--Database ID
			1,				--Page File ID
			1033784,		--Page ID, check with your own pageid
			3				--Commnad output style
			)

The highlighted page output tells that a 4 byte integer uniquifier is added.

This finishes this post. Thanks for reading.

Previous Related posts:

1. SQL Server 2012 Columnstore Index In Depth Part 1: Make a bigger AdventureworksDW database for Testing

2. SQL Server 2012 Columnstore Index In Depth Part 2: Columnstore Index Physical Data and Size Calculation