SQL Server Portal

Performance plays a vital role in SQL server. The importance of performance increases with the rise in the data growth. In other words, increase in data growth is directly proportional to the increase in performance. In SQL Server, index plays an important role to increase the performance as well. In SQL server 2012, we got a boost in performance (a new type of index came) namely ColumnStore Index. It is one of the nicest database features introduced in SQL Server 2012 built on xVelocity technology which is used to optimize memory. Lets discuss its syntax , purpose, examples & performance.

Syntax:
CREATE [ NONCLUSTERED ] COLUMNSTORE INDEX index_name

ON ( column [ ,…n ] )

[ WITH ( [ ,…n ] ) ]

[ ON {

{ partition_scheme_name ( column_name ) }

| filegroup_name

| “default”

}

]

[ ; ]

::=
{
[database_name. [schema_name ] . | schema_name . ]
table_name
{

::=
{
DROP_EXISTING = { ON | OFF }
| MAXDOP = max_degree_of_parallelism
}

Purpose :

The purpose of ColumnStore Index is to increase the performance in comparison to the traditional Index. It is a number of times faster than traditional index. The reason behind its performance is the way it handles the index. In Tradition Index, it stores multiple rows in each page and it retrieves in the same way. In ColumnStore Index, it stores each column in separate page and it retrieves in the same way.
In simple words you can say Tradition Index is a Row store while ColumnStore Index is a Column store.

Graphical Representation:
Given below is the graphical representation of traditional index & ColumnStore Index and you can see how each technology keeps the data pages in a different manner.

Let’s test the performance of ColumnStore index Step by Step.

Step 1 :
Create a sample table.

CREATE TABLE dbo.[PurchaseOrderDetail_Sample](
[PurchaseOrderID] [int] NOT NULL,
[PurchaseOrderDetailID] [int] NOT NULL,
[DueDate] [datetime] NOT NULL,
[OrderQty] [smallint] NOT NULL,
[ProductID] [int] NOT NULL,
[UnitPrice] [money] NOT NULL,
[LineTotal] numeric(18,2),
[ReceivedQty] [decimal](8, 2) NOT NULL,
[RejectedQty] [decimal](8, 2) NOT NULL,
[StockedQty] Numeric(18,2),
[ModifiedDate] [datetime] NOT NULL)

Step 2 :
Insert some data into table (PurchaseOrderDetail_Sample), because if table has few records you cannot measure the performance of columnstore index properly.

--This insertion is just to demonstrate,
--It is not recommended on production server.
Insert into dbo.[PurchaseOrderDetail_Sample]
Select * from [Purchasing].[PurchaseOrderDetail]
GO 100

Step 3 :
Next step is to create a Columnstore index in this table.

CREATE NONCLUSTERED COLUMNSTORE INDEX
[IX_PurchaseOrderDetail_Sample_ColumnStore]
ON [PurchaseOrderDetail_Sample]
(UnitPrice, OrderQty,ReceivedQty,ProductID)
GO

Step 4 :
Now, it is time to create a query using this table and view the performance. Remember this query has a ColumnStore Index.

SET STATISTICS TIME ON
SET STATISTICS IO ON
--This query will use columnstore index and will return the result set.
SELECT ProductID as [Product ID], AVG(UnitPrice) as [Average Unit Price]
,SUM(OrderQty) as [Purchase Order Qty], AVG(ReceivedQty) as [Received Qty]
FROM [dbo].[PurchaseOrderDetail_Sample]
GROUP BY ProductID
ORDER BY ProductID

go
--This query will ignore columnstore index and will return the result set.
SELECT ProductID as [Product ID], AVG(UnitPrice) as [Average Unit Price]
,SUM(OrderQty) as [Purchase Order Qty], AVG(ReceivedQty) as [Received Qty]
FROM [dbo].[PurchaseOrderDetail_Sample]
GROUP BY ProductID
ORDER BY ProductID
OPTION (IGNORE_NONCLUSTERED_COLUMNSTORE_INDEX)

SET STATISTICS TIME OFF
SET STATISTICS IO OFF

Step 5 :
Lets compare the result of IO & Time Statistics for both, with columnstore index and without columnstore index.

Execution Plan comparison :

Index Scan comparison :

Conclusion :
I am quite impressed to see the performance of Columnstore index. It is very handy and recommended in a place where you are either facing performance issues with the query or dealing with data warehouse. However, Columnstore index has some limitations as well which I will post in my upcoming post.