SQL server 2012 – Update column store index

Last thursday I presented some performance improvements at SQL Rally Post conference called SQL server 2012 Skills update, arranged by SQL service and Add Skills. The main focus of my presentation was regarding the new column store index and the performance differences between column store indexes, regular indexes and indexed views. As expected, the column store index was far more efficient than regular indexes and used much less space, but the indexed view based on an aggregate was even faster because the indexed view only had to store a fraction of the original data. The power of column store index is when you need to be able to do ad-hoc analysis and don’t want to or can’t create lots of indexed view. It’s not possible to create indexed views to handle all of the combinations of columns in a Fact table. I like the column store indexes and think it’s an excellent feature in the next version of SQL server.

One thing you have to think about is that the column store index is not updateable, and you should consider using partition switching (also called Sliding Window) to populate the table with new data. But how do you do that? Well it’s not that hard.

The steps you need to take is:

1. Create a partition function with some partition ranges
2. Create a partition schema
3. Drop the column store if you have one
4. Alter your Clustered index to use the partition schema
5. Create the column store index
6. Create another partition function with a partition range that is higher than the first partition function
7. Create a second partition schema for the second partition function
8. Create an exact copy of the first table (schema, not data)
9. Build the clustered index on the second partition schema
10. Load the data into the second table
11. Split the partition functions
12. Create a column store index on the second table
13. Alter the table and switch the partition into the first table -this will take only a fraction of a second! – the target partition has to be empty.
14. Repeat step 10 to 14 each day you need to load data.

Feel free to contact us if you have any questions regarding SQL server,.