Columnstore index and low selective columns

Question

I have a table with almost 1T rows.

create table bigtable (
  K1 int, K2 date, -- PK columns
  C1 ..., C8 ...., -- columns with various data types like float, date, varchar, ...
  B1 bit, B2 bit, ......, B10 bit -- 10 or so bit columns
  primary key (K1, K2)
)

I want to use columnstore to increase the query performance. There are the following options.

1. Create a cluster columnstore index
2. Create a noncluster columnstore index for all the columns
3. Create a smaller noncluster columnstore index for all the columns except these bit columns and other low selective columns?

The users will write queries themselves. A lot of the queries will be aggregation queries. Will the option 3 run as faster as the other two with smaller size?

I'm using SQL Server 2014. I can drop/recreate the nonclustered columnstore index periodically in my case because the data is not updated frequently.

Answer 1

One good feature of columnstore indexes is that only the columns which are necessary are read (unlike rowstores where the entire row is read).

So if you include all columns in the non-clustered index this will take longer to create the index but not adversely impact any query (and will benefit any query that uses those columns).

Answer 2

Question you need to ask yourself is: do I need to work on single rows? And it seems like you don't. And in that case, just put a Clustered Columnstore Index (your option 1). This will happen:

• Your table will be compressed
• Aggregation queries will be faster
• You don't need to think about which columns your users will use (unlike create a non-clustered index).

It just boils down to: will I have an application looking to read or update only one row, and will it happen often? And if this happens, you can always add a non-clustered rowstore index for that usage.