Introducing the Columnstore Index

Question

Post reply

Introducing the Columnstore Index

GattJ

SSC Veteran

Points: 250
More actions
August 6, 2014 at 9:40 pm

#397171

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

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

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

Carl Caputo SSC Veteran Points: 229 More actions · Answer 1

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 More actions · Answer 2

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 More actions · Answer 3

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: 235 More actions · Answer 4

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 More actions · Answer 5

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: 1004321 More actions · Answer 6

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)

lazy writer Ten Centuries Points: 1126 More actions · Answer 7

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 More actions · Answer 8

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 More actions · Answer 9

Great!!!

Thank you for sharing!

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

akljfhnlaflkj SSC Guru Points: 76202 More actions · Answer 10

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