Columnstore Indexes

SQL Server 2012 has a new method of storing nonclustered indexes. This is an index created just like any other, but it stores index data in a highly compressed, column-wise fashion. For certain classes of queries, particularly those found in Kimball-design star schemas, columnstore indexes make a lot of sense. Columnstore indexes can speed up data warehousing queries by a large factor, from 10 to even 100 times!

·         A columnstore index is just another nonclustered index on a table.

·         Columnstore indexes use their own compression algorithm; you cannot use row or page compression on a columnstore index.

·         Columnstore indexes accelerate data warehouse queries but are not suitable for OLTP workloads. Because of the row reconstruction issues, tables containing a columnstore index become read only. If you want to update a table with a columnstore index, you must first drop the columnstore index. If you use table partitioning, you can switch a partition to a different table without a columnstore index, update the data there, create a columnstore index on that table (which has a smaller subset of the data), and then switch the new table data back to a partition of the original table.

There are three new catalog views you can use to gather information about columnstore indexes:

·         sys.column_store_index_stats

·         sys.column_store_segments

·         sys.column_store_dictionaries

The columnstore index is divided into units called segments. Segments are stored as large objects, and consist of multiple pages. A segment is the unit of transfer from disk to memory. Each segment has metadata that stores the minimum and maximum value of each column for that segment. This enables early segment elimination in the storage engine. SQL Server loads only those segments requested by a query into memory.

Creating a columnstore index

CREATE TABLE #Customer

(

CustomerName varchar(200) NULL,

DateOfBirth datetime NULL,

Sex char(10) NULL,

Salary int NULL,

LoanAmount int NULL

)

You can specify only those columns you want to include, you would usually create a columnstore index across all of the columns.

CREATE NONCLUSTERED COLUMNSTORE INDEX csidxCustomer

ON #Customer (CustomerName, DateOfBirth, Sex, Salary, LoanAmount)

To drop a columnstore index

DROP INDEX #Customer.csidxCustomer

Disabling a columnstore index

ALTER INDEX csidxCustomer ON #Customer DISABLE

To rebuilding the index

ALTER INDEX csidxCustomer ON #Customer REBUILD

It is possible that under low memory conditions the below error might be seen:

Low memory condition:

The statement has been terminated.

Msg 8645, Level 17, State 1, Line 1

A timeout occurred while waiting for memory resources to execute the

query in resource pool 'default' (2).

Rerun the query.