Updatable Columnstore index in SQL Server 2014 (updateable column store Indexes)

The traditional relational database service engine is often not the best platform for analyzing and computing ultra-large amounts of data, so SQL Server has developed an analytics service engine to analyze and calculate large amounts of data. Of course, for the data storage platform SQL Server database engine, there is also a need for strong data processing power.

In SQL Server 2012, SQL Server introduced a Columnstore index that significantly provides the performance of high-traditional data warehouse type statements and has been further enhanced in SQL Server 2014. This article will further explain the changes in Columnstore indexes in SQL Server 2014, based on a brief introduction to the SQL Server 2012 Columnstore index.

As the name implies, Columnstore stores the data of a column separately, so there are two main benefits.

1. The similarity between the data in the same column is higher, so the compression ratio is higher. Disk operation, the disk IO will also be reduced accordingly. Of course, when the compressed data is read into memory, the decompression will require additional CPU.
2. Because the data is stored and read by column, if some columns are not needed in the access, then the actual operation will also not access these columns, then disk IO will be further reduced.

But if you just need to find a row or some line, you can do it directly with traditional index seek. The Index seek in traditional row storage is better.
sql Server 2012 has the following characteristics

• When the Columnstore index is loaded, not only is the index capability read-only, it cannot be modified, and the data of the underlying heap table or clustered index cannot be modified
• The memory required to create a Columnstore index tends to be more than the traditional index requires
• Incompatible with indexed views, filtered indexes, sparse columns, computed columns
• Support for common data types, but such as varchar (max), uniqueidentifier, etc. are not supported
• Very high data compression ratio
• High data pre-reading ratio
• The operating data unit is called batch
• Statement execution is a vector (vector-based)-based
• Statements are automatically factored into the use of Columnstore indexes when they are compiled

The column indexes of SQL Server 2014 mainly have the following characteristics

=========================================

In SQL Server2014, SQL Server developed the Columnstore index further, enabling it to support update operations. The major advances are as follows.

• Read and write support data
• support for more data types

When we have a clustered Columnstore index, we don't need a nonclustered column index, because all of the data is stored in columns at this time. However, if you need to add constraints on the table or if the workload still requires a nonclustered index of b-tree form, then we can only consider using nonclustered Columnstore indexes.

• The following improvements are performed on the execution of a statement
• The method of vector-based computation has been changed
• Support for more syntax
• All join methods (including outer, HASH, SEMI (not in, in)
• UNION All
• Scalar Aggregates
• "Mixed Mode" plans
• Further improvements to bitmap and spill operations
• Improvements to Hash join

As you can see, the Columnstore index of SQL Server2014 has made great progress, both in terms of functionality and performance.

The following is a brief description of how SQL SERVER2014 data modification operations for Columnstore indexes

==========================================

Let's begin by understanding the concept of design in the storage index:

1. Column are stored securely data is logically organized into a table that contains rows and columns, but the actual data is stored in columns.
2. Row are stored securely data is logically organized into a table that contains rows and columns, and is physically a row of data to store.
3. Row groups and column segments-when a column is stored, the data in the entire table is first cut by a certain number of rows, divided into groups called row group. Each column is stored separately for the data in each row group. Each column in the row group is called a segment.
4. Nonclustered Columnstore index– nonclustered Columnstore index is a read-only index that is created based on a heap table or clustered index, so that the data in the indexed column contains actually two copies, and the underlying table is also out of read-only mode.

The concepts described above are the same in the Columnstore index in SQL Server 2012 and 2014.
The following new content is added to the SQL Server 2014 Columnstore index:

1. Clustered Columnstore Inde-The entire table is organized according to the column storage, directly replaces the traditional heap table or the clustered index, and is free to make the pruning operation.
2. Delta Store-

Clustered Columnstore Index Although the structure of the column store is basically the same as the nonclustered Columnstore index, it can be used for pruning operations. The reason is that it has one or more pieces of storage, part of which is called Delta tore.

The newly inserted data is directly loaded into the Delta store and the delete operation only identifies the data as deleted, and the actual deletion needs to be done at rebuild.

The update operation is split into a delete operation and an insert merge completes.

If a BULK insert has a lot less than 100000 inserts, the data is loaded into the Delta store, otherwise it is loaded into columnstore.

when the amount of data in the Delta store exceeds 100 0000, "Tuple Mover" places the data which comes in the column store.  

is one of the clustered Columnstore indexes.

original link:http://blogs.msdn.com/b/apgcdsd/archive/2015/01/02/sql-2014-8-updateable-column-store-indexes.aspx

Updatable Columnstore index in SQL Server 2014 (updateable column store Indexes)