Introducing the Columnstore Index

  • GattJ

    SSC Veteran

    Points: 250

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

  • Carl Caputo

    SSC Veteran

    Points: 229

    Hi,

    I enjoyed your article.

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

  • sql_er

    SSCarpal Tunnel

    Points: 4135

    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?

  • Thomas Ballinger

    Valued Member

    Points: 50

    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.

  • Dazli

    SSC Veteran

    Points: 234

    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?

    Thanks

  • Carl Caputo

    SSC Veteran

    Points: 229

    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.

  • Jeff Moden

    SSC Guru

    Points: 994693

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

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

  • lazy writer

    Ten Centuries

    Points: 1066

    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 🙂

  • GattJ

    SSC Veteran

    Points: 250

    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 - http://rusanu.com/2011/07/13/how-to-update-a-table-with-a-columnstore-index[/url]

  • Daniel Braga

    Grasshopper

    Points: 23

    Great!!!

    Thank you for sharing!

    Daniel Braga
    http://bragadan.wordpress.com/

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    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 11 (of 11 total)

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