What are the challenges you face when working across database platforms? Take the survey
Options

Avoid Columnstore Indexes

shrekshrek Posts: 3
edited March 26, 2015 4:55PM in SQL Data Generator
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?

Comments

  • Options
    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
    Andrew Pierce
    Technical Sales Engineer
    Redgate Software
  • Options
    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.
Sign In or Register to comment.