I have seen column store index and also tested it. I have no doubt admiring that it is lightning fast. But every tutorial and blogs I have considered, says about its limitation "table will become read only" and I won't be able to insert/update/delete data in a table. And at the same time it is recommended to use them in data warehousing projects where data is in huge amount and updated periodically. On based of that I have come up with the solution to
- Disable index, do insert-update-delete and then rebuild the index
Will this be a right approach to deal with a large amount of data in fact table?
- Will the cost (in terms of time) in creating/rebuilding index can be more than regular process?
- Generally, data in fact table is queried until the cube is processed. I have a cube which works on views instead of table; so will it be cost efficient for me to keep a column store index on fact table?
- If I have a fact table which gets updated every day. So, will it be a good practice to disable or rebuild indexes?
- Do they behave similar to index in SQL Server? I mean, Select will be fast but update operation will take more time.