Introduction

In this article , I introduced one of fantastic features of Microsoft COLUMNSTORE and had a comparison between a rowstore table and a columnstore table. The result was very interesting.

However, if your table is in rowstore style, how we can convert a rowstore to columnstore

Solution

Converting a rowstore , there are two cases here

  • A rowstore table is heap
  • A rowstore table has a clustered index

Heap table

In this case, it is quite simple for us. We just need create a columnstore index on this table

CREATE CLUSTERED COLUMNSTORE INDEX [PK_SalesOrderDetailEnlarged_SalesOrderID_SalesOrderDetailID] ON [Sales].[SalesOrderDetailEnlarged]

Indexed table

If table has a clustered index, we need to drop it before creating a columnstore index on this table. In my example, I need to drop the constraint PK_SalesOrderDetailEnlarged_SalesOrderID_SalesOrderDetailID as the Primary Key and clustered index.

IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[Sales].[SalesOrderDetailEnlarged]’) AND name = N’PK_SalesOrderDetailEnlarged_SalesOrderID_SalesOrderDetailID’)
ALTER TABLE [Sales].[SalesOrderDetailEnlarged] DROP CONSTRAINT [PK_SalesOrderDetailEnlarged_SalesOrderID_SalesOrderDetailID]
GO
CREATE CLUSTERED COLUMNSTORE INDEX [PK_SalesOrderDetailEnlarged_SalesOrderID_SalesOrderDetailID] ON [Sales].[SalesOrderDetailEnlarged]

OK, the table is converted to columnstore format with a lot of improvement of performance.

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s