Columnstore indexes are introduced in SQL Server 2012. These are used for large tables where primarily READ operations are performed. Suitable environment is DWH fact tables. Storing data in this structure may help to get 10 X read performance and 10 X compression as compared to traditional row structured storage of data.
In this technology data is physically stored, retrieved and managed in a columnar data format. There are two types of columnstore indexes
- Clustered columnstore indexes (CCI)
- Non clustered columnstore indexes
Clustered columnstore indexes (CCI)
are used for large tables in DWH where considerable size difference may be acheived by compression. And READ operations are intensive.
Non clustered columnstore indexes
are used for environment where OLTP load is also involved with analysis queries.
Creating columnstore index on existing table
While creating a clustered columnstore index (CCI) on a table there may be some issue due to data type or column type. For example we may not create CCI on a table with data type nvarchar(max) in it. Or with a computed column (persisted or non persisted) in that table.
Combining columnstore and rowstore indexes
Starting from SQL Server 2016 we can have non clustered columnstore indexes on a table with rowstore table. And we can also have non clustered rowstore indexes on a columnstore table.
Get columnstore indexes in a DB
We can get general information about columnstore indexes in a DB with following script
SELECT schema_name(o.schema_id)+'.'+o.name AS TableName, i.name AS IndexName, i.type_desc AS IndexType FROM sys.indexes i INNER JOIN sys.objects o ON i.object_id = o.object_id WHERE i.type > = 4 ORDER BY TableName
Columnstore indexes on partitioned tables
Columnstore indexes can be implemented with partitioned tables by keeping these partition aligned.