Column Store Index Enhancements in SQL Server 2016

SQL Server provides following enhancements for column store indexes

Clustered Column Store Index Enhancements.

1. Support for PRIMARY KEY and FOREIGN KEYS.
2. Support for Non clustered ROW Store Indexes.

Non Clustered Column Store Index Enhancements

1. Support for Updates on the underlying table.
2. Support for Filtered Non Clustered Column Stored Index. 

Test Clustered Column Stored Index Enhancements

-- CREATE Test Table for Clustered Column Store Index.

;with cte_nums as (select

 row_number() over ( order by (select null) ) as rowno

  from sysobjects o1 cross join sysobjects o2 cross join sysobjects o3

  )

select isnull(rowNo,0) as ID, ABS(CHECKSUM(NEWID())) % 10000 Val1, ABS(CHECKSUM(NEWID())) % 1000 Val2

into dbo.TestCCI

from cte_nums

where rowNO <= 1100000

-- CREATE Clustered Column Store Index.

create clustered columnstore index ccixTestCCI on dbo.TestCCI

------------------------------------------------

--- PRIMARY KEY SUPPORT ------------------------

-- CREATE Primary Key---------------------------

------------------------------------------------

alter table dbo.TestCCI add constraint PK_TestCCI primary key(ID)

------------------------------------------------------

--- Support for Non Clustered Index. -----------------

-- CREATE Non Clustered Index ------------------------

-------------------------------------------------------

create index IX_TestCCI_Val2 on TestCCI(Val2)

-- Check the Space Utilization by Indexes.

SELECT i.name AS Index_Name

    ,SUM(s.used_page_count) * 8 AS SizeKB

FROM sys.dm_db_partition_stats AS s

INNER JOIN sys.indexes AS i ON s.object_id = i.object_id

    AND s.index_id = i.index_id

where object_name(i.object_id) = 'TestCCI'

GROUP BY i.[name]

ORDER BY i.[name]

-- The RowStore Indexes use More Space than COlumnStore Indexes.

Index_Name	SizeKB
ccixTestCCI	5312
IX_TestCCI_Val2	24072
PK_TestCCI	28432

-- Index Usages

select count(*) from dbo.TestCCI where ID > 1000000 -- Uses Clustered Column Store Index

select Max(Val2),Min(Val2) from dbo.TestCCI -- Uses RowStore Index (IX_TestCCI_Val2)

select SUM(Val2) from dbo.TestCCI -- Uses Clustered Column Store Index.

Test Non Clustered Column Stored Index Enhancements

-- CREATE Test Table for Non-Clustered Column Store Index.

;with cte_nums as (select

 row_number() over ( order by (select null) ) as rowno

  from sysobjects o1 cross join sysobjects o2 cross join sysobjects o3

  )

select isnull(rowNo,0) as ID, ABS(CHECKSUM(NEWID())) % 10000 Val1, ABS(CHECKSUM(NEWID())) % 1000 Val2

into dbo.TestNCCI

from cte_nums

where rowNO <= 1100000

-- CREATE Non-Clustered Column Store Index.

create columnstore index nccixTestCCI on dbo.TestNCCI (id, val1, val2)

--------------------------------------------------------------------------

--- Non Clustered Column Store Index Supports Updates. -------------------

---- Update the table having Non Clustered Column Store Index ------------

--------------------------------------------------------------------------

update TestNCCI

set Val2=10 where ID=1000000

-- Check the Space Utilization by Indexes.

SELECT i.name AS Index_Name

    ,SUM(s.used_page_count) * 8 AS SizeKB

FROM sys.dm_db_partition_stats AS s

INNER JOIN sys.indexes AS i ON s.object_id = i.object_id

    AND s.index_id = i.index_id

where object_name(i.object_id) = 'TestNCCI'

GROUP BY i.[name]

ORDER BY i.[name]

-- The ColumnStore Index uses less space compared to table.

Index_Name	SizeKB
NULL	27256
nccixTestCCI	8904

---- Testing Filtered Non-Clustered Column Store Indexes.

;with cte_nums as (select

 row_number() over ( order by (select null) ) as rowno

  from sysobjects o1 cross join sysobjects o2 cross join sysobjects o3

  )

select isnull(rowNo,0) as ID, ABS(CHECKSUM(NEWID())) % 10000 Val1, ABS(CHECKSUM(NEWID())) % 1000 Val2

into dbo.TestFilteredNCCI

from cte_nums

where rowNO <= 1100000

--------------------------------------------------------------------------

--- Filtered Non Clustered Column Store Index Support. -------------------

---- Create Filtered Non Clustered Column Store Index  ---------------------

--------------------------------------------------------------------------

create columnstore index FilteredNCCIXTestCCI on dbo.TestFilteredNCCI (id, val1, val2) where id < 1000000

-- Index Usages

select sum(val2) from dbo.TestFilteredNCCI where ID > 1000000 -- Uses table

select sum(val2) from dbo.TestFilteredNCCI where ID < 1000000 -- Uses Filtered Column Store Index