Columnstore Indexes are introduced in SQL Server 2012. Below are some of the features of columnstore indexes and various awesome links to learn more about columnstore indexes.
Features of ColumnStore Indexes:
a) Columnstore indexes are built for read-only table(s) as of now. It may change that it allows operations apart from reading, in the future. If you try to do any operation viz. insert or update the data, you get an exception.
b) Column store indexes are used to build a view on top of an existing table.
c) The view fetches only those columns which are needed.
d) Performance is 10x to 100x faster.
Links on Columnstore Indexes in order to learn more about this new feature:
Columnstore Indexes [Link]
This MSDN post includes sections like Columnstore Indexes Described, Columnstore Index Restrictions and Limitations, eColumnstore Indexes with a Partitioned Table, Typical Columnstore Index Scenarios and much more.
SQL Server Columnstore Index FAQ (en-US) [Link]
This post includes FAQs on topics like Creating a Columnstore Index, Limitations on Creating a Columnstore Index, More Details on Columnstore Technology, Using Columnstore Indexes, Managing Columnstore Indexes, Batch Mode Processing etc.
Columnar Storage in SQL Server 2012 [Link]
This paper outlines the design of column store indexes and batch-mode processing and summarizes the key benefits this technology provides to customers. It also highlights some early customer experiences and feedback and briefly discusses future enhancements for column store indexes.
Understanding New Column Store Index of SQL Server 2012 [Link]
This is an awesome introduction to Column Store Index including topics like How Column Store Improves the Performance of the Query, When to Use Column Store Index, Limitations of Using Column Store Index etc.
xVelocity in SQL Server 2012 Delivers Order-of-Magnitude Gains for Data Warehouse Queries [Link]
SQL Server 2012 Performance White Paper [Link]
SQL Server 2012 Performance White Paper which talks about xVelocity for Data Warehousing, xVelocity for Business Intelligence etc.
SQL Server 2012 : Columnstore Index in action [Link]
In summary, for warehousing workloads, a columnstore index is a great addition to the database engine with significant performance improvements even on reasonably small data sets. It will re-define the ‘index-light’ approach that the SQL Server Fast Track Data Warehouse methodology champions and help simplify warehouse based performance tuning activities. Will it work in every scenario? I very much doubt it, but it’s a good place to start until we get to experience it live in the field.
Column Store Internals [Link]
SQL Server 2012: Columnstore Index accelerates query 100X [Link]
SQL SERVER – Fundamentals of Columnstore Index [Link]