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:

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.

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