Avoid Columnstore Indexes

shrek

We want Data Generator to generate some sample data into a fact table on SQL Server 2014. For some reason, Data Generator detects columnstore indexes that exist on two dimension tables with foreign key constraints to the fact table and even detects a columnstore index on a temporary table that has no FKs to the fact table. The Data Generator suggests to disable the columnstore indexes, generate the data, and enable the indexes back.
1. Why is it doing this? How does it detect the columnstore indexes?
2. Can we bypass the columnstore index disable/enable steps?

Andrew P

Hello Shrek,

Thanks for posting and I am sorry if this is causing you any issues!

A table with a nonclustered columnstore index is read-only until the index is dropped or disabled. To update the table and the nonclustered columnstore index you can switch partitions in and out. You can also disable the index, update the table, and then rebuild the index. We take the latter route most likely because it seems to be the least memory intensive operation. There is no way to bypass this behavior at present.

Here is a msdn article on columnstore Indexes https://msdn.microsoft.com/en-us/librar ... x#dataload
and specifically on nonclustered columnstore indexes https://msdn.microsoft.com/en-us/librar ... spx#change

I hope that was helpful! I will follow up with you via email for more Q and A.

Warm Regards,
Andrew Pierce
Redgate Support Engineer

shrek

Andrew,
Thanks for the reply. I am aware of the CS dynamics and that's not an issue. The issue is that the tool wants to drop and recreate CS indexes on any table that has a CS index in the database, even though the table doesn't have FK constraint to the table I want to populate. Please read my original post carefully. I'm NOT generating data in a table that has a CS index.