Column-Store Indexes
Column-Store index is designed for improving query performance which is used for a heavy
amount of data like data warehouse fact tables. This type of index stores the
index data in column-based rather than row-based. This index is introduced in version
SQL server 2012; basically this is a column-based non-clustered index.
Why should we use Column-Store Index
Column-Store
index provides a very high level of compression, typically by 10 times, to
significantly reduce your data warehouse storage cost. For analytics, a column-store
index offers an order of magnitude better performance than a B-Tree index.
Columns
store values from the same domain and commonly have similar values, which
result in high compression rates. I/O bottlenecks in your system are minimized
or eliminated, and memory footprint is reduced significantly.
High
compression rates improve query performance by using a smaller in-memory
footprint. In turn, query performance can improve because SQL Server can
perform more query and data operations in memory.
Batch
execution improves query performance, typically by two to four times, by
processing multiple rows together.
Queries
often select only a few columns from a table, which reduces total I/O from the
physical media.
Note:
1- Use a clustered column-store index to store
fact tables and large dimension tables for data warehousing workloads. This
method improves query performance and data compression by up to 10 times.
2- Use a non-clustered column-store index to
perform analysis in real-time on an OLTP workload.
How to create a column-store index
Using the following script you can
create the column-store index in SQL Server.
--to create a column-store index
CREATE COLUMNSTORE
INDEX IX_OrderDetail_ProductIDOrderQty_ColumnStore
ON Sales.OrderDetail (ProductId,OrderQty);
|
Source
of content from Microsoft: column-store index
