Stairway to Columnstore Indexes Level 1: A First Look At Columnstore Indexes

  • yb751 (6/1/2016)


    Hugo Kornelis (5/30/2016)


    yb751 (5/30/2016)


    This is great Hugo!

    On a side note it might have been worth mentioning that you'll need an enterprise edition of SQL to try it out.

    You are right, I should have added that.

    By the way, for testing it you can also use Developer Edition. That edition has all the features of Enterprise Edition without the cost (but obviously not licensed for production use). This used to be a bargain at I think 50 bucks or so, and recently Microsoft announced that Developer Edition will be completely free going forward. Not sure if that "completely free" applies to the SQL Server 2016 version only or also to older versions.

    I could be wrong but I believe the free developer edition only applies to 2014 & 2016. My local copy for testing is 2012 so that I can mirror my production environment. However, I may consider also installing 2014 just so I can at least test some of the features I currently don't have access to.

    That's my understanding too ... 2014 onwards.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • Great article Hugo. Very well researched and written. I just started the series and look forward to the rest.

    I have neen reading up on columnstore indexes recently and just read 0b]What You Need to Know about the Batch Mode Window Aggregate Operator in SQL Server 2016: Part 1[/b]. Ben-Gan talks about how to trick the optimizer into us I'll ml get Batch mode even when a columnstore index is not present. Very cool stuff.

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

  • Great article series. We're now looking into an upgrade to sql 2016 in order to take advantage of column store indexes and in-memory-oltp. Currently we have one main customer database of about 5.5 TB which has a mix of perhaps 75% OLTP and 25% reporting-style queries.

    What I'm looking for first is documentation on "gotchas" in either of those technologies for our situation.

    Currently on sql 2012 Enterprise.

  • Indianrock (1/7/2017)


    Great article series. We're now looking into an upgrade to sql 2016 in order to take advantage of column store indexes and in-memory-oltp. Currently we have one main customer database of about 5.5 TB which has a mix of perhaps 75% OLTP and 25% reporting-style queries.

    What I'm looking for first is documentation on "gotchas" in either of those technologies for our situation.

    Currently on sql 2012 Enterprise.

    This stairway series was started before SQL 2016 was out, and I made the choice to not shift focus half-way. All levels currently out, as well as level 12 (that I am currently writing on), focus on SQL 2012 and SQL 2014.

    Instead of shifting halfway to include SQL 2016, I have chosen to add one extra level (probably going to be level 13), which will cover all changes in SQL Server 2016. However, these changes are very manifold, so I will only be able to scratch the surface in that single level.

    A very exhaustive and in-depth coverage of columnstores can be found in the blog series written by Niko Neugebauer, the technical editor for this stairway. See http://www.nikoport.com/columnstore/[/url]. It is an extremely long series. Starting from part 54 the improvements and changes in SQL 2016 are explained. (The 53 levels before that focus mainly on SQL 2014 and generic architecture; they were written before previews of SQL 2016 were available)


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • Excellent and well-written.  Unfortunately I'm still on SS 2008!  However, that changes in January. So, hopefully I get a chance to practice this technology.  Thanx!

    Wallace Houston
    Sunnyland Farms, Inc.

    "We must endeavor to persevere."

Viewing 5 posts - 16 through 20 (of 20 total)

You must be logged in to reply to this topic. Login to reply