A "columnstore” index within Microsoft SQL Server 2012 stores each column in a separate set of disk pages, rather than storing multiple rows per page which is the way data is stored traditionally.
Benefits of this are:
- Only the columns needed to solve a query are fetched from disk (this is often less than 15% of the columns in a typical fact table)
- It is easier to compress the data due to the redundancy of data within a column
- Buffer cache hit rates are improved because data is highly compressed, and frequently accessed parts of commonly used columns remain in memory, whereas infrequently used parts are paged out
- On average this technology increases query performance by a factor 10 compared to traditional row based systems
There is one limitation with columnstore indexes within SQL Server : you cannot directly update tables with index "columnstore” using INSERT, UPDATE, DELETE, and MERGE statements, or bulk load operations. To load data into a table with columnstore index, you need to disable the index before you load the data and then the index needs to be rebuilt. This can be quite a long task if you have very large tables. You can use "switch partition” to overcome this problem and disable the index columnstore only on the new partition that you have just created.
The "columnstore” index is of great use in a Data Warehouse environment. It reduces the time spent on query performance optimization and the need to create additional aggregate tables. This function will probably create a new interest in relational reporting solutions and it will also boost the performance and usage of the lower level layers of your Data warehouse.
We can only regret that the columnstore index option is not part of the SQL Server Business Intelligence edition (in terms of licensing), even though it will be intensively used for Business Intelligence applications. You will have to buy the Enterprise License of SQL Server 2012 to benefit from the columnstore index-functionality.