Sunday, January 10, 2016

Creating a ColumnStore Index using the 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. This post uses the SQL Server 2012 Developer Edition for demonstration.

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

PowerShell Pro Tools is a Visual Studio Community 2017 extension that you can try free

I see there is at least one extension, PowerShell Pro Tools that you can download. You can find it in the Visual Studio Community 2017...