Can I a table have both Clustered Columnstore Index and Nonclustered Columnstore Index?
I am seeing example strategies to use Clustered Columnstore Indexes with only Nonclustered Binary row indexes.
When I try creating both a Clustered Columnstore and Nonclustered Columnstore I receive error:
Msg 35339, Level 16, State 1, Line 7
Multiple columnstore indexes are not supported.
No, you can't:
CREATE TABLE dbo.cs_test (id INT NOT NULL)
CREATE CLUSTERED COLUMNSTORE INDEX cs_whatever ON dbo.cs_test
CREATE NONCLUSTERED COLUMNSTORE INDEX nc_whatever ON dbo.cs_test(id);
Returns the error message:
Msg 35339, Level 16, State 1, Line 7 Multiple columnstore indexes are not supported.
The same result for multiple nonclustered columnstore indexes, as well.
There would be little meaning to allow for this. The columns aren't ordered in the index anyhow. Just include all columns that you want in the one and only col-store index that you can have (or if it is clustered, then all columns are already included).
(One could argue that several col-store indexes could be beneficial if we had better control over the build phase in order to control the ability to do row-group elimination. But that would in the end make it difficult for the optimizer in order to decide which col-store index to use unless there would be some statistics over the max-min values in each row-group. Unless you have looked into the internals of col-store index this might not mean much to you... :-) )
