SQL Server 2016: Updatable, Non-Clustered Columnstore Indexes

Write for InfoQ

Non-clustered Columnstore Indexes (NCC Index) are also getting some enhancements in SQL Server 2016. The most notable of these enhancements is the ability to be updated.

First offered in 2012, Non-clustered Columnstore Indexes were designed to be read-only snapshots of a normal heap or B-Tree table. The idea is that you would drop and rebuild them on a regular basis, perhaps during a nightly or weekly maintenance cycle. Alternately, you could use partition swapping to load more data.

When Clustered Columnstore Indexes (CC Index) were created for SQL Server 2014, the columnstore engine was upgraded to support data modification. But this functionality wasn’t extended to NCC indexes, which were still limited to snapshot mode only.

With 2016, this limitation has been completely removed. In fact, the new default is for NCC Indexes to be updatable. If you want the original snapshot semantics, then you have to store the index in a read-only filegroup.

Filtered Indexes

When you know that you only need a well-defined subset of the data, a filtered index can dramatically reduce the amount of disk space you need. And in many cases, filtering can also boost performance.

Another new feature of NCS Indexes in SQL Server 2016 is the ability to apply filters to the index definition. This is only available on Non-clustered Columnstore Indexes, the clustered and in-memory varieties still need to encompass the entire

Batch Mode Enhancements

Batch mode processing is a rather esoteric topic that only makes sense when you understand how the SQL Server interpreter works. So if you are not familiar with the topic, check out Chris Adkin's series titled Under The Hood With SQL Server Batch Mode. Briefly, batch mode is at least 2 times faster than processing one record at a time, but it can’t always be used.

If you are running compatibility mode 130 (i.e. SQL Server 2016), then these operations are now candidates for batch mode.

• SORT
• Aggregates with multiple distinct functions: COUNT/COUNT, AVG/SUM, CHECKSUM_AGG, STDEV/STDEVP.
• Window aggregate functions: COUNT, COUNT_BIG, SUM, AVG, MIN, MAX, and CLR.
• Window user-defined aggregates: CHECKSUM_AGG, STDEV, STDEVP, VAR, VARP, and GROUPING.
• Window aggregate analytic functions: LAG< LEAD, FIRST_VALUE, LAST_VALUE, PERCENTILE_CONT, PERCENTILE_DISC, CUME_DIST, and PERCENT_RANK.

In SQL Server 2014, batch mode was only available for parallel queries. Now it can also be used for single-threaded work.

Related Topics:

• Architecture & Design
• SQL Server 2016
• Columnar Databases
• SQL Server

We protect your privacy.