Now in the SQL Server 2014 not only nonclustered columnstore index but also clustered columnstore index is available. The clustered columnstore index helps us improve mainly data compression and query performance for data warehousing. A table with a clustered columnstore index cannot have any type of nonclustered index and a table with a clustered columnstore index cannot have unique constraints, primary key constraints, or foreign key constraints.
Nonclustered column store index provides a significant query performance improvement for relational data warehouses by grouping and storing data for each row and then join all the rows to complete the whole index.
Let us see if the above statement is actually true in action:
I am going to use a ContosoRetailDW database for this demonstration. Since I can’t find the same database for SQL Server 2014 I will just simply use the 2012 version and change the compatibility.
------------------------------------------------------------------------ -- Change the compatibility of the database-- ------------------------------------------------------------------------ USE master GO ALTER DATABASE ContosoRetailDW SET COMPATIBILITY_LEVEL = 110 GO |
I am going to make a copy of FactSales table first so that I don’t have to change the original table.
------------------------------------------------------------------------ -- Create a copy of FastSales table ------------------------------------------------------------------------ USE ContosoRetailDW; GO IF OBJECT_ID('dbo.FactSales_IndexTable', 'U') IS NOT NULL DROP TABLE dbo.FactSales_IndexTable GO SELECT SalesKey ,DateKey ,channelKey ,StoreKey ,ProductKey ,PromotionKey ,CurrencyKey ,UnitCost ,UnitPrice ,SalesQuantity ,ReturnQuantity ,ReturnAmount ,DiscountQuantity ,DiscountAmount ,TotalCost ,SalesAmount ,ETLLoadID ,LoadDate ,UpdateDate into FactSales_IndexTable FROM ContosoRetailDW.dbo.FactSales; go |
Now let us check the size of the table before building the clustered columnstore index.
------------------------------------------------------------------------ -- Check the space used before index -- ------------------------------------------------------------------------ sp_spaceused N'dbo.FactSales_IndexTable'; go |
We have around 419216/1024 = 409 MB of data size on disk. Okay lets create our index then check the size of table
------------------------------------------------------------------------ -- Create Clustered columnstore index -- ------------------------------------------------------------------------ USE ContosoRetailDW GO CREATE CLUSTERED COLUMNSTORE INDEX ClusteredColumnStoreIndex ON dbo.FactSales_IndexTable WITH (DROP_EXISTING = OFF); GO ------------------------------------------------------------------------ -- Check the space used after creating clusted columnstore index -- ------------------------------------------------------------------------ sp_spaceused N'dbo.FactSales_IndexTable'; go |
We have around 76728/1024 = 74 MB of data size on disk. That is around 18% of compression which is great if we are dealing with large size table and we are short in disc space.
The other advantage of clustered columnstore index over the nonclustered one is we could insert and update our table without having to do additional work as in the case of nonclustered one
------------------------------------------------------------------------ -- inert into table with clusted columnstore index -- ------------------------------------------------------------------------ insert into dbo.FactSales_IndexTable with(tablock) SELECT top(1) DateKey, channelKey, StoreKey, ProductKey, PromotionKey, CurrencyKey, UnitCost, UnitPrice, SalesQuantity, ReturnQuantity, ReturnAmount, DiscountQuantity, DiscountAmount, TotalCost, SalesAmount, ETLLoadID, LoadDate, UpdateDate FROM dbo.FactSales; go |
Once we updated our table we could rebuild our index without having to drop and recreate them using
ALTER INDEX ClusteredColumnStoreIndex ON dbo.FactSales_IndexTable REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = COLUMNSTORE); go |
Okay let us get rid of the clustered column store index so that we could work with our nonclustered one.
------------------------------------------------------------------------ -- Drop clustered columnstore index -- ------------------------------------------------------------------------ USE [ContosoRetailDW] GO DROP INDEX [ClusteredColumnStoreIndex] ON [dbo].[FactSales_IndexTable] WITH ( ONLINE = OFF ) GO |
Just for demonstration purpose let us simply create a nonclustered columnstore index on all columns we have.
------------------------------------------------------------------------ -- Create Nonclustered columnstore index -- ------------------------------------------------------------------------ USE ContosoRetailDW GO CREATE NONCLUSTERED COLUMNSTORE INDEX NCColumnStoreIX_FactSales_IndexTable ON dbo.FactSales_IndexTable ( SalesKey, DateKey, channelKey, StoreKey, ProductKey, PromotionKey, CurrencyKey, UnitCost, UnitPrice, SalesQuantity, ReturnQuantity, ReturnAmount, DiscountQuantity, DiscountAmount, TotalCost, SalesAmount, ETLLoadID, LoadDate, UpdateDate ) WITH (DROP_EXISTING = ON) GO |
But this time if we simply execute the above insert statement we will get error similar to this one.
To work around it we could disable the nonclustered columnstore index, do the insertion then rebuild the index.
------------------------------------------------------------------------ -- disable nonclustered columnstore index -- ------------------------------------------------------------------------ USE [ContosoRetailDW] GO ALTER INDEX [NCColumnStoreIX_FactSales_IndexTable] ON [dbo].[FactSales_IndexTable] DISABLE GO ------------------------------------------------------------------------ -- inert into table with nonclustered columnstore index -- ------------------------------------------------------------------------ insert into FactSales_IndexTable with(tablock) SELECT top(1) DateKey, channelKey, StoreKey, ProductKey, PromotionKey, CurrencyKey, UnitCost, UnitPrice, SalesQuantity, ReturnQuantity, ReturnAmount, DiscountQuantity, DiscountAmount, TotalCost, SalesAmount, ETLLoadID, LoadDate, UpdateDate FROM dbo.FactSales; go ------------------------------------------------------------------------ -- rebuild nonclustered columnstore index -- ------------------------------------------------------------------------ USE [ContosoRetailDW] GO ALTER INDEX [NCColumnStoreIX_FactSales_IndexTable] ON [dbo].[FactSales_IndexTable] REBUILD PARTITION = ALL GO |
This time we are able to insert to our table. Once you rebuild your index it will enable the index and you will no longer be able to insert to the table. If you have different partitions on the table the script used above will also change to the appropriate partition.
Read more:
http://technet.microsoft.com/en-us/library/gg492088.aspx
http://msdn.microsoft.com/en-us/library/gg492153(v=sql.120).aspx
