Saturday, January 9, 2016

How do you create a columnstore index in SQL Server Management Studio?

ColumnStore index is  described in this post .

Of course you need Enterprise edition of SQL Server 2012 ColumnStore Index as on no other version it is supported.

However, if you have a developers edition of SQL Server 2012 you can see a few basic steps as described here.

Here are some step-by-step screen shots to create the  index

We will be creating the ColumnStore index for the Employees table which has already the following indexes.

In order to create a ColumnStore Index (only one per table) right click Indexes node and choose the Non-Clustered Columnstore Index as shown.

This brings up the New Index window as shown. The windows comes up with a default name for the index which you change or modify.

Modify or change name and click on the Add... button as there should be at least one column for the index. When you click on Add... button, the Select Columns from 'dbo.Employees' windows is displayed as shown.

Place check mark for the columns to be included. Here in three columns will be added. Preferably the data types of columns are either int or nvarchar. The one likely to be advantageous are those that are repeated in the table. Here Country, City and Reports to column are added.

Click OK and the information gets into the New Index window.

Click OK on the New Index window and the index is created as shown in the Indexes node in Object Explorer.

 You can look for this in sys.indexes as shown

No comments: