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.
- Create a cluster columnstore index
- Create a noncluster columnstore index for all the columns
- 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.