Introducing the Columnstore Index

  • Comments posted to this topic are about the item Introducing the Columnstore Index

  • Hi,

    I enjoyed your article.

    Quick typo spotted - page 3 title should be titled "Gender" not "Designation" in the columnstore page examples.

  • How does one deal with the fact table becoming read only once this index is added?

    What is the suggested best practice when data needs to be added on a regular basis?

  • I was wondering the same thing, do we have to drop the index and put it back each night during the loads from our ETLs. I am about to use this on a number of aggregate tables to increase speed across multiple millions of rows table but if I cant add rows each night it is not viable to use a technology while on the 2012 stack.

  • Hi,

    Just a little bit confused. When create a clustered columnstore index, if all columns will be included, is that the same as rowstore index? What's the benefit?


  • With 2012 you have to drop the columnstore index each time, then rebuild if inserting new records and only use nonclustered columnstore. I believe with SQL 2014 you can amend the table without dropping the columnstore, as well as use clustered columnstores for first time.

    All columns are converted but depending on the query usage you will see more benefit, but it's the Xvelocity in memory engine (same as that used in PowerPivot) that produces the massive performance benefits VS traditional row store indexes.

  • Has anyone actually used this feature in production, yet?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • we have used it for one of our DWH - they are performing really good when our analysis team runs their daily radon analysis queries.

    for small table we will drop and recreate them during the load.

    for big fact tables we will use partition switching where we will load the data into stg table and create column-store index and then it will be switched to the jumbo table 🙂

  • There are two ways to update a table with Columnstore index in 2012:

    i) The usual disable index - load data - rebuild index routine

    ii) Efficiently use table partitioning to switch partitions

    I found a neat example of doing this at this link -[/url]

  • Great!!!

    Thank you for sharing!

    Daniel Braga

  • Well we don't have it yet, but someday we'll update and this info will come in handy. Thanks.

Viewing 11 posts - 1 through 10 (of 10 total)

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