BT

SQL Server 2016: Updatable, Non-Clustered Columnstore Indexes

| by Jonathan Allen Follow 576 Followers on Jun 03, 2015. Estimated reading time: 1 minute |

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.

Rate this Article

Adoption Stage
Style

Hello stranger!

You need to Register an InfoQ account or or login to post comments. But there's so much more behind being registered.

Get the most out of the InfoQ experience.

Tell us what you think

Allowed html: a,b,br,blockquote,i,li,pre,u,ul,p

Email me replies to any of my messages in this thread

SQL is obsolete and dead by Ilya Geller

For the past 70 years SQL (generic name for whatever IBM did) dominated search for electronic information. It's external to data technology, which helps to distill patterns and statistics based on queries, from outside to data, externally. SQL technology emanates from External Relations theory of Analytic Philosophy: students of Moore, Russell and Wittgenstein established IBM and everybody followed their path.
However, there is Internal Relations theory, which is based on Bradley, Poincare and my ideas. In this theory patterns and statistics are found into structured data.
I discovered and patented how to structure any data: Language has its own Internal parsing, indexing and statistics. For instance, there are two sentences:

a) 'Sam!’
b) 'A loud ringing of one of the bells was followed by the appearance of a smart chambermaid in the upper sleeping gallery, who, after tapping at one of the doors, and receiving a request from within, called over the balustrades -'Sam!'.'

Evidently, that the 'Sam' has different importance into both sentences, in regard to extra information in both. This distinction is reflected as the phrases, which contain 'Sam', weights: the first has 1, the second – 0.08; the greater weight signifies stronger emotional ‘acuteness’.
First you need to parse obtaining phrases from clauses, restoring omitted words, for sentences and paragraphs.
Next, you calculate Internal statistics, weights; where the weight refers to the frequency that a phrase occurs in relation to other phrases.
After that data is indexed by common dictionary, like Webster, and annotated by subtexts.
This is a small sample of the structured data:
speaking - done - once : 333112
speaking - was - both : 333109
place - is - in : 250000
To see the validity of technology - pick up any sentence.

Do you have a pencil?

Can SQL provide statistics as weights? IBM, Oracle, etc. - all of them - ask to assign weights manually.
IBM, Oracle, etc. use stop words - imagine you must skip some very frequently used words specking?

External Relations theory is external to data - it describes how it looks like. However, any description is subjective, it always omits something. If you try to picture data based on queries - you always miss a lot!
Internal Relations captures everything, it structures everything - articles, pronouns, particles, everything what data contains.

All IT industry is obsolete, everything - thanks to Moore, Wittgenstein and Lord Russell.

Allowed html: a,b,br,blockquote,i,li,pre,u,ul,p

Email me replies to any of my messages in this thread

Allowed html: a,b,br,blockquote,i,li,pre,u,ul,p

Email me replies to any of my messages in this thread

1 Discuss

Login to InfoQ to interact with what matters most to you.


Recover your password...

Follow

Follow your favorite topics and editors

Quick overview of most important highlights in the industry and on the site.

Like

More signal, less noise

Build your own feed by choosing topics you want to read about and editors you want to hear from.

Notifications

Stay up-to-date

Set up your notifications and don't miss out on content that matters to you

BT