Columnstore Index In SQL Server 2014

Overview

In an organization data is considered as a primary key or part. Data is crucial for any organization. As data grows database size increases and  its respective table size also increases. In a previous firm the main application of that firm's INET banking was in SQL database and was in SQL 2005, and as a result certain tables like customer details, account balance size increased day by day in GBs. To find a particular record from October searching a customer required lot of time and as a result server memory was increasing and it was a painful task to search a record in that particular table because already data was huge and to find a record was a pain. Due to this they needed to do purging activity on quarterly basis (i.e. after 3 months) they take data which is required from date to date and the  rest of the data is moved into a different server.

As these are some of the drawbacks of SQL Server 2005 and so on till SQL Server 2008.

Since SQL server 2012 they have introduced column store index. As the term clearly tells you data is stored in column wise format. So let’s see what column store index is about.

Introduction

In SQL Server everyone is aware that queries which we type in query window results or output are displayed in grid view format. Technically, SQL server stores data logically in terms of rows and columns that are stored in disks in row store format inside data pages. Now here data pages are nothing but a table in which your data i.e. rows and columns are stored in a single data page. As in SQL Server indexes (clustered index or non-clustered index) data is stored in row wise format or structure. Column Store index stores data in a columnar format.

Row Wise Structure,



“PAGE N"

As a diagram here the data is stored in columnar format. As you can see I have given that column as PAGE N if there are n rows and columns now imagine for a search condition the time required and I/O request for the hardware as well.

Now let’s see a column store index how it is stored.



Just compare two figure and you will be able to see the difference in columnstore index; each column has its representative Pages. As a result the query tuning, indexing or you can say search condition becomes easier the resultant outtime is less as compared to previous figure. Lets start by practically comparing how much I/O time more in these two by creation table and indexes .

Lets Start

• Open SQL Management Studio.
  
  
  
  
• Lets Create Database TEST.
  
  
  
  Let's Create Table Employee.
  
  
• Type Query SELECT * from Employee.
  
  
  
  
• Now let's Create a Clustered index on this Table Employee.
  
  As you can see clustered index is made in DB.
  
  
  
  Now just run SELECT * from Employee command and check the execution plan.
  
  
  
  Now let's check its Estimated I/O cost and Estimated CPU Cost in order to compare with ColumnStore Index . Just Mouse hover on Clustered index scan and you will get a yellow popup.
  
  
  
  The highlighted section here is Estimated I/O cost 0.003125 and Estimated CPU Cost  0.000158.
  
  
• Now let's create nonclustered index on the same table and let's compare
  
  
  
  (JUST type Command Create nonclustered cloumnstore index [indexname] on tablename (field1,field2,…field n)
  
  As you can see a nonclustered columnstore index appeared as below:
  
  
  
  Now let's see the execution plan on the same table.
  
  
  
  Now you can clearly compare the output here.
  
  Now you can also see by using command SET STATISTICS IO ON.
  
  Refer the screenshot below:
  
  

Common Questions

When to Use ColumnStore Index ?

Column Store indexing is mainly used to see the performance of the queries which have regular scanning aggregarte functions and have n number of joins on that table use column store index . Column store index is mainly used to solve data warehouse related questions . Eg :- OLAP Cubes , OLTP transactions and so on .

Note: Using ColumnStore index makes a table read-only . Whn you will try you will find that its doesnot update value in table you have to disable the cloumnstore index. Hence while inserting data into table it fails.

Disadvantages

When you start working with Column store index you will see disadvantages such as:

• Column store index can only on nonclustered index.
• Only one column store index allowed per table.
• Cannot be unique not able to combine with primary or foreighn keys.
• It cannot be altered once it is created like ALTER INDEX INDEXNAME . you need to drop and recreate it.

Conclusion

These disadvantages I found out while working; let me know what you found.