BT

Facilitating the Spread of Knowledge and Innovation in Professional Software Development

Write for InfoQ

Topics

Choose your language

InfoQ Homepage News SQL Server 2016: Updatable, Non-Clustered Columnstore Indexes

SQL Server 2016: Updatable, Non-Clustered Columnstore Indexes

Bookmarks

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
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.

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

Community comments

  • SQL is obsolete and dead

    by Ilya Geller,

    Your message is awaiting moderation. Thank you for participating in the discussion.

    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

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

BT