Using column store indexes on table
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 &amp; 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.