On data tables with a large number of columns, it might be beneficial to consider a column store index. As the name would imply, column store indexes organize the columns of a data table, rather than the rows. It’s a subtle distinction, but this arrangement allows SQL Server to fit more values in memory and devote resources to just the columns that are being called for, rather than loading the entire width of a data table into memory, when searching for particular records.
Columnstore index is a new type of index introduced in SQL Server 2012. It is a column-based non-clustered index geared toward increasing query performance for workloads that involve large amounts of data, typically found in data warehouse fact tables.
Performance advantages in columnstore indexes are possible by leveraging the VertiPaq compression technology, which enables large amounts of data to be compressed in-memory. This in-memory compressed store reduces the number of disk reads and increases buffer cache hit ratios because only the smaller column-based data pages that need to satisfy a query are moved into memory.
For wide tables, such as those commonly found in data warehouses, columnstore indexes come in handy as you essentially reduce the amount and size of data needed to be accessed for any given query.
Sources:
Comments