What is COLUMNSTORE Index in SQL Server?

Leave a comment (0) Go to comments

What is COLUMNSTORE Index ?

The SQL Server Denali (2011) launched a new type of index called the columnstore.

  • This new index, combined with enhanced query optimization and execution features, improves data warehouse query performance by hundreds to thousands of times in some cases
  • This can routinely give a tenfold speedup for a broad range of queries fitting the scenario for which it was designed.
  • It does all this within the familiar T-SQL query language, and the programming and system management environment of SQL Server.
  • It’s is fully compatible with all reporting solutions that run as clients of SQL Server, including SQL Server Reporting Services.
  • To improve query performance, all you need to do is build a columnstore index on the fact tables in a data warehouse.

How COLUMNSTORE Index Works ?

A columnstore index stores each column in a separate set of disk pages, rather than storing multiple rows per page as data traditionally has been stored. A Quick difference between column store and row store can be found here

Download White Paper from Microsoft to more about

  • Columnstore Indexes
  • How to create Columnstore Indexes
  • Performance Illustration
  • Benefits of Columnstore Indexes
EOF - What is COLUMNSTORE Index in SQL Server?, SQL Server 2012 , 2008 R2 , 2008 , 2005 , 2000

Leave a Reply

Your email address will not be published. Required fields are marked *


You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Notify me of followup comments via e-mail. You can also subscribe without commenting.