SQL Server 2016: In-Memory Columnstore Indexes

Write for InfoQ

New for SQL Server 2016 is the ability to place a Columnstore Index on a Memory Optimized Table. To understand what that means, we should first define the terms Columnstore Index and Memory Optimized Table.

A Columnstore Index is an index that organizes its data in terms of columns instead of rows. Each block of data represents one column with up to one million rows. So if you had five columns and ten million rows, you would have 50 blocks of data. This organizational strategy is particularly effective when you only want a subset of the columns, as the columns you don’t care about aren’t read from disk.

Columnstore indexes are significantly faster than table scans, but not quite as fast as traditional B-Tree style indexes. This makes especially suited for ad hoc reports where you can’t predict which indexes will be needed.

A Memory Optimized Table is just what it sounds like, a table that is optimized to be held in memory at all times. This allows for numerous benefits such as lock-free writes, but it comes with significant limitations. For example, you are only allowed eight indexes, which can be quite limiting for a table being used for ad hoc queries.

SQL Server 2016 partially compensates for this limitation by allowing one of those eight indexes to be a Columnstore Index. But there are rules that you need to follow:

• Like other indexes on a Memory Optimized Table, the Columnstore Index must be defined when the table is created.
• The Columnstore Index must include all columns in the base table. (This limitation doesn’t exist for Columnstore Indexes on normal tables.)
• The Columnstore Index must include all rows in the base table. Or in other words, it can’t be a filtered index.

A related feature for Memory Optimized Tables is the ability to create natively compiled queries. These are queries that, instead of using the SQL Server interpreter, are compiled to machine code using the C compiler. Queries that use the Columnstore Index can’t use this option, and instead always run through the interpreter.

Related Topics:

• Architecture & Design
• In-Memory Databases
• SQL Server 2016
• Columnar Databases
• SQL Server

We protect your privacy.