Search code examples
sql-serverindexingdata-warehouse

Benefit of combining a clustered columnstore index and a nonclustered columnstore index?

I ran into a question about indexing a large fact table in a data warehouse asking to implement two indexes in order to optimize performance and the answer suggested to create:

  • a clustered columnstore index (CCI)
  • and a nonclustered columnstore index (NCCI) on part of the columns

As a CCI includes all the table's columns, I would have thought that there would be no point in combining two columnstore indexes (one clustered and one nonclustered) and rather go for :

  • a clustered columnstore index (CCI)
  • and a nonclustered rowstore index on part of the columns

(see microsoft documentation on columnstore index design guidance)

What would be the benefit, if there is any, of combining two columnstore indexes (one clustered and one non clustered) on a large fact table in a SQL Server database / data warehouse ?

The question that triggered my question can be found on this page, question 70. I don't know if I am missing something or if their solution is inaccurate.

If anyone could help me out, it would be great!

Solution

  • As a CCI includes all the table's columns, I would have thought that there would be no point in combining two columnstore indexes

    Indeed it's not even allowed. If you try you'll get

    Msg 35339, Level 16, State 1, Line 5
Multiple columnstore indexes are not supported.

    The question

    enter image description here

    Since Index1 contans all the data and is optimized for aggregations, it should be a clustered columnstore index.

    And so he correct answer for the Index2 is a a unique nonclustered index with included columns, where any non-key columns are the included ones.