Implementing Indexes - Microsoft SQL Server 2012 Performance Tuning - page 222

6. Create a clustered index again, for future use, and remove the columnstore index, so

that the table will no longer be a read-only table:

--removing columnstore index
DROP INDEX idx_columnstore_refno ON ordDemo
GO
--creating clustered index again for
--future use
CREATE CLUSTERED INDEX idx_refno ON ordDemo(refno)
GO

How it works...

The columnstore index uses Microsoft's VertiPaq technology, which is why columnstore indexes
don't have to fit in the main memory. However, they can effectively use as much memory as is
available on the server. Portions of columns are moved in and out of memory on demand.

As per the image given in the introduction to this recipe, this technology stores column(s) in
a single page, which results in faster searches than the rowstore index. Keep in mind that
once you add a columnstore to a table, you cannot delete, insert, or update the data, as it is
read-only. However, since the columnstore will be mainly (but not necessarily) used for data
warehousing, this should not be a big problem.

Step 2 shows the execution plan of the SELECT query that was using the rowstore
non-clustered index, and step 5 shows the execution plan of the SELECT query that
was using the columnstore index for the same query.

Here are the differences in performance between both the approaches, taken from the
screenshot given in steps 1 and 3:

Operator Cost before non-custered index Cost after non-clustered index

Estimated I/O Cost

0.358681 0.003125

Estimated Operator Cost 0.452945 (90%) 0.113282 (20%)

Estimated CPU cost 0.942642 0.110157

Estimated Subtree Cost

0.452945 0.113282