SQL Server 2016: Memory Columnstore Index

Author Jonathan Allen, translator Sherry

A new feature of SQL Server 2016 is the ability to add Columnstore Index (Columnstore Index) on memory-Optimized tables (Optimized table). To understand what this means, we should first interpret the term Columnstore index and memory-optimized table.
A Columnstore index is an index that organizes data by columns rather than rows. Each chunk stores only one column of data, with a maximum of 1 million rows. Therefore, if the data is 5 columns and 10 million rows, then it needs to be stored in 50 data blocks. This data organization strategy is particularly effective when querying only a subset of columns, because the database does not read from disk the columns that the user does not care about.
Columnstore indexes are much faster than table scans, but not as fast as traditional B-tree indexes. This is especially useful for instant reports that cannot predict what indexes are needed.
Memory-Optimized table as its name is, it is a table that has been optimized and resides in memory. This has many benefits, such as lock-independent writing, but it also has a lot of limitations. For example, only 8 indexes are allowed, which is too restrictive for tables that are used for instant queries.
SQL Server 2016 makes up for this limitation, which allows one of the 8 indexes to store the index in the column. But follow these rules:? Like other indexes on a memory-optimized table, a Columnstore index must be defined at the time the table is created. The Columnstore index must contain all the columns in the base table. (There is no such restriction on the Columnstore index on the normal table.) ) The Columnstore index must contain all the rows in the base table. In other words, it cannot be a "filtered index (filtered").
A feature associated with memory-optimized tables is the creation of a locally compiled query. The database compiles these queries into machine code using the C compiler instead of using the SQL Server interpreter. Queries that use Columnstore indexes can use this option instead of always running through the interpreter.

View English text : SQL Server 2016:in-memory Columnstore Indexes

SQL Server 2016: Memory Columnstore Index