This new type of Index is introduced with new upcoming 2011 version of SQL Server. This new index improves the query performance by hundreds to thousands of times in some cases but in routinely gives at least ten times acceleration to your queries. This index will be more useful in dataware house databases.
Why this is called as columnstore Index?
A columnstore index stores each column as a separate set of disk pages, rather than storing multiple rows per pages which are called as rowstore.
The difference between rowstore type and this new columnstore type is shown in figure below.
create columnstore index [Index_name] on [table_name]([column_name], [column_name],…..)
When you create the columnstore index on a table they store all data for separate columns on separate pages. This improves I/O scan performance and buffer cache hit ratios.
When and what kind of tables benefited from this?
You will create columnstore index on a Fact table also on dimensions tables if you have extremely large dimensions for example more than 10 million rows then you will be creating this indexes and run queries against those tables you all will see wonder that they ran really very fast usually more than 100 times, said by Microsoft.
- It improves performance by decreasing the need to fetch data from disk to memory.
- Accelerates query performance by more than 100 times to 1000 times in some cases
- You can get interactive response time for queries against billions of rows.
- For this year “Denali” release the table with columnstore indexes can’t be updated directly using INSERT, Update or Merge.
- In order to update table with Columnstore index, the index needs to be dropped then update the table and again re-index columnstore but this should be not a problem if we need high performance which saves lots of time to your business.
In short time we will comeup with more info on this columnstore index once sql server 2011 released.
Thanks for reading this post….Follow @sqlfrndz