What is the purpose of column store index on database tables, and in what situations should they be used (with OLTP)?
closed as too broad by James Anderson, Marco, mustaccio, Tom V, MDCCL Dec 13 '16 at 15:26
Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.
SQL Server columnstore indexes are created for datawarehouse purposes.
When should I use this?
I would use a CLUSTERED columnstore index standard for most fact tables, because you will get a better read performance & it is good for writing data to it! note: there is a big difference in the way you insert data. you should read up on that on this post. http://www.nikoport.com/2013/07/06/clustered-columnstore-indexes-part-6-observing-the-behavior/
However, since the SQL Server 2016 you can use columnstore indexes to combine a OLAP workload on your OLTP system.
This is done by using the filtered NON-CLUSTERED columnstore index. The way to do this is by filtering your data between hot and warm data. The filtered columnstore index will then keep a copy of the filtered warm data part.
What is Hot and Warm data?
F.E. You have a customer who buys one banana, then he wants to change his order and makes it one banana and 2 apples, then he cancels one apple. This is typical hot part of your data. Which has a lot of update and inserts and deletes. After he pays for this order the order becomes a warm portion of the data which means only that the data will not change frequently.
The trick is putting a non-clustered index with a filter to only have the warm data inside your non-clustered columnstore index. In this case it could be a flag payed or something similar. This is something that is different in every company.
Why should you use this?
You will get an incredible read speed activity due to the batch mode processing.This will greatly increase your large read queries on the system.
NON-Clustered columnstore trick
There is also a trick using an empty filtered non-clustered columnstore index. F.e.
CREATE NONCLUSTERED COLUMNSTORE INDEX TESTCCI on dbo.TEST ( ID ) where ID = -1 and ID = -2
This will create an empty nonclustered columnstore index (SQL Server will also not read any data to create the index). After you do this your read queries will be able to benefit from batch mode processing and thus greatly improving your big read queries on your oltp system.
The columnstore index is intended for datawarehousing. SQL Server takes a copy of the column and basically zips it up. Compression algorithms ensure the index has a very small footprint on the hard disks. This moves the burden of querying the index from IO to buffer and CPU. (DW solutions require lots of RAM.) Due to the way the compression algorithms work you have no control over sorting. The overhead of rebuilding the index as tables are updated makes it unsuitable for OLTP systems, however Microsoft are doing their best to improve on this situation with major improvements being made in SQL Server 2016.