SQL Server 2016 - ColumnStore Clustered Index vs. Non-ColumnStore Clustered Index

Question

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.

Answer 1

Clustered Columnstore Indexes are designed to be used primarily with Data Warehouse, OLAP or reporting solutions. They are not great at WHERE x = y type queries; they are excellent at WHERE x < 1 and x > 1000000 type 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.