Columnstore Index

Traditional indexes are based on rows where data is grouped and stored in row basis and then join all rows to complete the entire index. Columnstore indexes store data for columns and join the columns to complete the indexes. This type of index is helpful when retrieving data from large tables such as database warehouse fact tables for queries such as queries for filtering, aggregating, grouping.

Let us create a Columnstore index and compare the results with standard non clustered index.

Expand the Indexes tab inside a table where you want to create the Columnstore index. Right click Indexses and select New Index. Under this, you will see in the below image, a new option called Non-Clustered Columnstore Index…

With this you will be taken to the index creation dialog box. Here, Date column is used to create the Non-Clustered Columnstore Index for FactFinance table.

Similarly, you can use T-SQL scripts to create Columnstore Indexes. Following script will create an index on FactFinace table in the AdventureWorksDenali database.

USE [AdventureWorksDWDenali]

GO

CREATE NONCLUSTERED COLUMNSTORE INDEX [csi_date] ON [dbo].[FactFinance]

(

[Date]

) ON [PRIMARY]

GO

To compare the results of Columnstore index with traditional index, let us create standard Non-Clustered Index on the same date column as shown in the below dialog box.

So Index creation also has a different interface. Also, in SQL Server 2012 filtered index creation has an interface support which is not there in previous versions.

After creating those indexes you will see following indexes in the SQL Server Management Studio.

So you have a different icon for the Columnstore index.

Let us try to retrieve some data using above to indexes.

You will see that same query is executing at but with different indexes. More rows are added to this table so that you can visualize the different. This table has 2522176 Rows.

USE [AdventureWorksDWDenali]

GO

SELECT DISTINCT [DATE]

FROM dbo.FactFinance WITH ( INDEX = [csi_date])

ORDER BY [DATE]

GO

SELECT DISTINCT [DATE]

FROM dbo.FactFinance WITH ( INDEX = [nci_date])

ORDER BY [DATE]

GO

Let us first analyze the execution plans for both queries.

From the above image, you can see that Query 1 (which uses the Columnstore Index) has 4 % cost while the Query 2 (which uses the Non-Clustered index) has cost of 96 %.

Let us now examine these queries with SET STATISTICS IO, TIME ON

	With Columnstore Index	With Non-Clustered Index
CPU Time	16 ms	451 ms
Execution Time	35 ms	2,986 ms
Read Count	49	18,316

Above results will tell you that columnstore is far better than the standard index. However, you need to analyze this case by case as for some cases having a Columnstore index will have negative impact.

 

Limitations

Following are the limitations of Columnstore Indexes.

· Cannot be Clustered Key or Primary Key

· Columnstore index has to be a Non-Clustered Index and it can be neither Clustered Key nor Primary Key.

· Cannot update tables with Column and following error will return if you try to update a table which has a Columnstore index.

Msg 35330, Level 15, State 1, Line 1

INSERT statement failed because data cannot be updated in a table with a columnstore index. Consider disabling the columnstore index before issuing the INSERT statement, then rebuilding the columnstore index after INSERT is complete.

· You can have only once Columnstore Index per table. After creating the first Columnstore index, option to create Columnstore Index will be disabled and if you try to create the index using T-SQL script following error will be returned.

Msg 35339, Level 16, State 1, Line 2

Multiple nonclustered columnstore indexes are not supported.

· Cannot be created with the INCLUDE keyword.

· Cannot be a unique index.

· Cannot be created on a view or indexed view.

· Cannot include a sparse column.

· Cannot have more than 1024 columns.

· If you need this much of columns for the Columnstore indexes, then obviously there is something wrong with the design.

· The following data types can be included in a columnstore index.

o Char, varchar, nchar and nvarchar (except varchar(max) and nvarchar(max))

o decimal (and numeric) (Except with precision greater than 18 digits.)

o int, bigint, smallint, and tinyint

o float and real

o bit

o money and smallmoney

o All date and time data types (except datetimeoffset with scale greater than 2)

· The following data types cannot be included in a columnstore index.

o binary and varbinary

o ntext, text, and image

o varchar(max) and nvarchar(max)

o uniqueidentifier

o rowversion (and timestamp)

o sql_variant

o decimal (and numeric) with precision greater than 18 digits

o datetimeoffset with scale greater than 2

o CLR types (hierarchyid and spatial types)

o xml

· Cannot Modify

Simply you are not allowed to use ALTEX INDEX syntax against Columnstore Indexes to modify the index. Drop and re-create the Columnstore index instead. Of course, if you want to disable or enable index, you can use ALTER INDEX syntax.