SQL Server 2016--feature enhancements for clustered Columnstore indexes

Author

• Jonathan Allen, translator Shao Sihua released on June 14, 2015

The Clustered Columnstore index (CC index) is one of the two most compelling features in SQL Server 2014 and is designed to be used for data tables with more than 10 million records. The user does not need to specify the index explicitly, also can guarantee the good performance of analytic query.

However, there is a flaw in this feature in version 2014, where the user cannot specify an index. Although the CC index is much faster than the table scan of a traditional table, it is not a manually adjusted overlay index. Therefore, in order to support both modes, the developer has to create two tables: a normal table with a B-tree index structure, and a table with a clustered Columnstore index. Obviously, keeping these two tables in sync is a big challenge.

In SQL Server 2016, this problem will no longer exist. Clustered Columnstore indexes in compatibility level 130 (that is, SQL Server 2016) can contain a B-tree-style level two index, just like a traditional data table. This index supports any number of columns and can be filtered.

Another problem with clustered Columnstore indexes is the lack of support for primary keys and foreign keys. Because the database cannot enforce referential integrity for a CC index, only the developer of the middle tier can guarantee that there will be no data corruption. Now that you can add a B-tree index to the CC index, the user can "create a primary key and foreign key using a B-tree index to enforce these restrictions".

Warning: Once the B-tree index is defined for the clustered Columnstore index, the merge cannot be used. ”

Isolation level

Starting with SQL Server 2016, the CC index supports both the snapshot and read-commit snapshots isolation levels, eliminating the need for read-write locks, and also makes the tables that write data frequently perform better.

Index defragmentation

The CC index in SQL Server 2014 is designed to be used primarily to add data-style operations. Although it is also possible to modify and delete records, this results in index fragmentation, which can be eliminated only by rebuilding the entire CC index. In 2016, fragmentation can be reduced by re-organizing the index operations. The re-organization of an index is an online operation, which means that it does not require a long time to occupy the lock and therefore prevents query operations on the table.

Enhancements to Batch Mode

Because the CC index and the nonclustered Columnstore index use the same storage engine, its batch mode is also enhanced.

View English text: SQL Server 2016:clustered Columnstore Index Enhancements

Thank long Zhang for the review of this article.

SQL Server 2016--feature enhancements for clustered Columnstore indexes