SQL SERVER – Replace Rowstore Clustered Index with Columnstore Clustered Index

A very interesting scenario happened while I was working with my client on Comprehensive Database Performance Health Check. After careful research, we identified that they can take the help of columnstore clustered index for their table. Let us learn in this blog post how to Replace Rowstore Clustered Index with Columnstore Clustered Index.

The table on which he wanted to create already has a clustered index on it. He ran the following command assuming that the query will drop the existing rowstore column store index and create a new clustered columnstore index. However, that did not happen and the query gave the error.

Here is the command:

CREATE CLUSTERED COLUMNSTORE INDEX [PK_TestTable] ON [dbo].[TestTable]
WITH (DROP_EXISTING = ON);

Here is the error:

Msg 1907, Level 16, State 1, Line 1
Cannot recreate index ‘PK_TestTable’. The new index definition does not match the constraint being enforced by the existing index.

It was very clear from the experiment that one can’t just command DROP_EXISTING = ON to drop rowstore index and create clustered columnstore index. If you want to create a clustered columnstore index, you either have to create that on the HEAP table or first drop your rowstore clustered index. There is no command to Replace Rowstore Clustered Index with Columnstore Clustered Index.

Here is another video where I explain how DROP_EXISTING work.

Here are my few recent videos and I would like to know what is your feedback about them.

• Copy Database – SQL in Sixty Seconds #169
• 9 SQL SERVER Performance Tuning Tips – SQL in Sixty Seconds #168
• Excel – Sum vs SubTotal – SQL in Sixty Seconds #167
• 3 Ways to Configure MAXDOP – SQL in Sixty Seconds #166
• Get Memory Details – SQL in Sixty Seconds #165
• Get CPU Details – SQL in Sixty Seconds #164
• Shutdown SQL Server Via T-SQL – SQL in Sixty Seconds #163
• SQL Server on Linux – SQL in Sixty Seconds 162
• Query Ignoring CPU Threads – SQL in Sixty Seconds 161
• Bitwise Puzzle – SQL in Sixty Seconds 160
• Find Expensive Queries – SQL in Sixty Seconds #159
• Case-Sensitive Search – SQL in Sixty Seconds #158
• Wait Stats for Performance – SQL in Sixty Seconds #157
• Multiple Backup Copies Stripped – SQL in Sixty Seconds #156

Reference: Pinal Dave (http://blog.SQLAuthority.com)