Column store Index is one of the new feature introduced by Microsoft in sql server 2012 edition.
This is a new kind of indexing which provides significant performance for queries & ssas for aggregations etc.
In a regular indexing , each row is stored on a page where as in the column store index each column is stored per page which makes it much more efficient for queries such a aggregations etc.. which will have specific columns in it. They are highly compressed , so easy to store large volume of data in memory resulting in very low IO cost.
How ever there are some limitations , few are mentioned below :
1) they are read only, once created inserts/ updates are not possible, this can be used in warehousing applications where data is loaded periodically & before data load index can be dropped or disabled & then created once the data load is finished.
2) there can be only one column store index per table
3) it cant be clustered index.
4) cant be pk or fk
5)cant be used with tables that has cdc or file stream
Reading material :
http://social.technet.microsoft.com/wiki/search/searchresults.aspx?q=SQL+Server+Columnstore
http://blog.sqlauthority.com/2011/10/29/sql-server-fundamentals-of-columnstore-index/
http://blog.sqlauthority.com/2011/11/07/sql-server-video-performance-improvement-in-columnstore-index/
http://blog.sqlauthority.com/2011/11/06/sql-server-updating-data-in-a-columnstore-index/
http://blog.sqlauthority.com/2011/10/30/sql-server-how-to-ignore-columnstore-index-usage-in-query/
This is a new kind of indexing which provides significant performance for queries & ssas for aggregations etc.
In a regular indexing , each row is stored on a page where as in the column store index each column is stored per page which makes it much more efficient for queries such a aggregations etc.. which will have specific columns in it. They are highly compressed , so easy to store large volume of data in memory resulting in very low IO cost.
How ever there are some limitations , few are mentioned below :
1) they are read only, once created inserts/ updates are not possible, this can be used in warehousing applications where data is loaded periodically & before data load index can be dropped or disabled & then created once the data load is finished.
2) there can be only one column store index per table
3) it cant be clustered index.
4) cant be pk or fk
5)cant be used with tables that has cdc or file stream
Reading material :
http://social.technet.microsoft.com/wiki/search/searchresults.aspx?q=SQL+Server+Columnstore
http://blog.sqlauthority.com/2011/10/29/sql-server-fundamentals-of-columnstore-index/
http://blog.sqlauthority.com/2011/11/07/sql-server-video-performance-improvement-in-columnstore-index/
http://blog.sqlauthority.com/2011/11/06/sql-server-updating-data-in-a-columnstore-index/
http://blog.sqlauthority.com/2011/10/30/sql-server-how-to-ignore-columnstore-index-usage-in-query/
No comments:
Post a Comment