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.

  • Are you able to try out the options on a sample of your data? My guess is that (3) will gain you fairly little over (1). The bit columns can compress very, very well (see test script), and maybe some queries will need to reference those columns. It seems like the primary problem might be that users will be writing their own queries against a 1.3T row table. Unless the users are very knowledgeable and very skilled with query plan analysis, I imagine there are a lot of ways that can go poorly very quickly. Jun 9, 2016 at 18:22

2 Answers 2


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).


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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.