White Papers
27 BP1062 | Best Practices for Decision Support Systems with Microsoft SQL Server 2012 using Dell EqualLogic PS Series
Storage Arrays
6.3.2 Columnstore index benefits
Columnstore Index offers query performance and space saving benefits for SQL Server data warehouse
database applications.
• Space Savings: The columnstore index is compressed when created. On creation, it uses the
VertiPaq™ compression algorithm, which compresses the data more than either
page
or
row
compression. These indexes are much smaller than b-tree indexes.
• Query Performance: The optimizations used to speed up the query processing here are:
- Index compression: Columnstore Indexes are much smaller which allows faster index scans.
- Optimized memory structures: Columnstore Index uses its own memory pool and does not
use fixed size pages from the buffer pool provided sufficient memory is available for SQL
Server.
- Parallelism: The batch mode feature enables for parallel executions.
There can be both row store index and column store index on the same table. The query optimizer will
decide when to use the column store index and when to use other types of indexes.
6.3.3 Limitations of using columnstore index
Some key limitations of columnstore index listed by Microsoft are stated below. For all other detailed
limitations refer to the MSDN Library article titled, “Basics: Columnstore Index Restrictions and Limitations”
at http://msdn.microsoft.com/en-us/library/gg492088.aspx#Restrictions
• Creating a columnstore index makes the table read-only.
• Columnstore index might not be the ideal choice for selective queries, which touch only one (or
a few) rows or queries that lookup a single row or a small range of rows. Ex: OLTP workloads
• Cannot be clustered. Only non-clustered columnstore indexes are available.
• Cannot act as a primary key or a foreign key.
• A columnstore index on a partitioned table must be partition-aligned.
• A column store cannot be combined with
-
page
or
row
compression
- Replication
- Change tracking
- Change data capture
- File stream