create index columnstore

Question

create index columnstore

abdalah.mehdoini

Ten Centuries

Points: 1015
More actions
November 29, 2021 at 1:11 pm

#3957823

hello ,
Who has any idea please how to rebuild a clustered index to a clustered columnstore index
thanks

Viewing 4 posts - 1 through 3 (of 3 total)

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

Grant Fritchey SSC Guru Points: 398890 More actions · Answer 1

You really can't. A clustered index, columnstore or rowstore, defines data storage. You can't really redefine data storage without completely rebuilding the table. So, build a new clustered columnstore table and move the data over.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Jeff Moden SSC Guru Points: 1004378 More actions · Answer 2

abdalah.mehdoini wrote:

hello ,
Who has any idea please how to rebuild a clustered index to a clustered columnstore index
thanks

Documentation is your friend. This is an example specifically covered in the following Microsoft Documentaiton and it wasn't difficult to find, My search was "columnstore index, creating" and the first "hit" returned was...

https://docs.microsoft.com/en-us/sql/t-sql/statements/create-columnstore-index-transact-sql?view=sql-server-ver15

Then go look for example "B", which has the example.

Be advised that, behind the scenes, it works just like Grant's suggestion works. It creates the new index first and, if successful, then it drops the old index or heap. That does temporarily require more space than the original table.

Since it seems like you've not actually read the documentation for ColumnStore, make sure you do! ColumnStore is NOT a panacea of performance and there recommendations of what you should use it for and why. Invoking a ColumnStore Clustered Index may actually cause you problems if you use it for the wrong thing.

Start by reading the following article and then spend some time reading some of the other links cited in that article, as well.

https://docs.microsoft.com/en-us/sql/relational-databases/indexes/columnstore-indexes-overview?view=sql-server-ver15

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

Grant Fritchey SSC Guru Points: 398890 More actions · Answer 3

Jeff is so very right.

Columnstore is magic... until it isn't. It really comes down to what kind of queries you're running most of.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning