We all know about the two different types of indexes i.e. clustered and non-clustered indexes. However starting from SQL Server 2012 a new index was made available i.e. Columnstore index.
Traditional indexes group and store row data but the columnstore index on the other hand stores data by columns instead of by rows. It then joins all the columns to complete the index. This way when a query is fired the columnstore index retrieves only the required columns thus reducing the amount of data retrieved from storage hence increasing the turn around time and thereby maximizing performance.
Columnstore index makes use of compression. Normally except in the case of columns which make up the primary key or any candidate key the rest of columns have duplicate records i.e. they are redundant. So compression here is used to store these grouped data in columns in a way where redundant data need not be written again and again. Thus columnstore index saves us a lot of space while making use of compression. It also increases performance by saving most used columns in memory thus saving time spent while reading from disk.
The syntax of columnstore index is given below:
CREATE NONCLUSTERED COLUMNSTORE INDEX columnstoreindexname ON Table1 (column1, column2, column3); GO