SQL : ColumnStore Index (part I)

Introduction

ColumnStore indexes are a powerful addition to SQL Server and it started with SQL Server 2012 and it continued to evolve to a better ColumnStore Index today. I want to learn more about it and it did some investigation about the columnstore Index. So, this blogpost is not a in depth blogpost but a reflection of some basics of the columnstore index.

Now, I'm working with SQL Server 2016 and it is possible to create a column store table with the CREATE TABLE statement. Unfortunately my client is working on SQL Server 2014 and therefore I won't use that.

Playground

First, let's start with getting some data from the WorldWideImporters database.

SELECT * 
INTO Sale
FROM WideWorldImportersDW.[Fact].[Sale]

Create the ColumnStore

Now, we have a table Sale in a database (I've called it ColumnStoreTest). Because we are pretending that we are working on SQL Server 2014 we create the ColumnStore Index with the CREATE CLUSTERED COLUMNSTORE INDEX command

       
CREATE CLUSTERED COLUMNSTORE INDEX CCI_Sale ON [Sale];  
GO  

How do we know whether we created a Column Store Table? Well, go the properties of the table and click on the Storage tab and here you can see the CompressionType as ColumnStore (actually, I'm not sure whether this is also in SQL Server 2014).

Drop the ColumnStore

Next stop is dropping the ColumnStore with the DROP INDEX statement:

       
DROP INDEX CCI_Sale ON [Sale]
GO  
 

This will drop the index and it will return to the row store. The CompressionType is set to none.

Conclusion 

First discovery of the ColumnStore Index: how to build and how to drop a columnstore index.

Greetz,

Hennie