White Papers

25 BP1062 | Best Practices for Decision Support Systems with Microsoft SQL Server 2012 using Dell EqualLogic PS Series
Storage Arrays
6.3 Columnstore index studies
Columnstore index is a new feature in SQL Server 2012 that can be used to speed up the processing time
on common data warehousing queries. This section evaluates the benefits of columnstore indexing on a
TPC-H DW database, by implementing columnstore indices on the largest tables and then comparing its
performance against the database without columnstore indexing.
6.3.1 Understanding columnstore index
Columnstore index groups and stores data for each column and then joins all the columns to complete
the index. These differ from traditional indexes which group and store data for each row and then join the
rows to complete the index.
For some types of queries, the SQL Server query processor can take advantage of the columnstore layout
to significantly improve query execution times. SQL Server columnstore index technology is especially
appropriate for typical data warehousing data sets. Columnstore indexes can transform the data
warehousing experience for users by enabling faster performance for common data warehousing queries
such as filtering, aggregating, grouping, and star-joining queries. Read the MSDN Library article
“Columnstore Indexes” at http://msdn.microsoft.com/en-us/library/gg492088.aspx
Row store versus columnstore indexes Figure 9