I just read the SQL 2016 Columnstore Index guide located here. We're going to have some rather large tables (hundreds of millions to billions of rows) in a SQL 2016 database that will be used for both OLTP and analytics.
These tables will be queried, primarily, one of two ways: 1) Users will retrieve rather small result sets based on specific discrete values in a Where clause (e.g., Where SubId = 'ABC'); 2) Users will retrieve larger results sets based on a date/time value ranges (e.g., Where ReadTime Between '2/1/2017' And '2/5/2017').
Since a columnstore index is better suited for scenerio #2 (I think), I'm considering making the clustered index non-columnstore (e.g., on SubId) for scenerio #1 and creating a non-clustered columnstore index (e.g., on ReadTime) for scenerio #2.
However, I'm not sure this is really any better than if I created a columnstore clustered index on ReadTime and a non-columnstore index on SubId.
I'm not sure how to make this decision.
Best Answer
Clustered Columnstore Indexes are designed to be used primarily with Data Warehouse, OLAP or reporting solutions. They are not great at
WHERE x = ytype queries; they are excellent atWHERE x < 1 and x > 1000000type queries where the query processor can perform a range-scan on the CCI.Assuming your workload is OLTP-critical, I'd recommend, without a ton of work on my part, to create the table with a standard B-Tree clustered index, and consider a well-designed nonclustered columnstore index that will help with your point #2.
If the OLTP component of the workload is small, or not very critical, but the reporting component is critical, you could possibly create the table as a clustered columnstore index, then add a number of starndard b-tree nonclustered indexes to help support the OLTP component.