ColumnStore Index In SQL Server

What is columnstore Index?

• A columnstore index is a type of data structure, which is used to store, manage and retrieve the data by using columnar data format.
• This method of storing data is in columns on a single page.
• It stores the data in the compressed format.
• Columnstore index is more efficient and the whole index can be saved in the memory, which makes queries faster.

Columnstore Index varieties

• Clustered columnstore index
• Non- clustered columnstore index

Syntax
Clustered columnstore index is given below.

1. CREATE ClUSTERED COLUMNSTORE INDEX [IX_Emp_ColumnStore]  
2. ON dbo.[Employees]   

Syntax
Non-clustered column store index is given below.

1. CREATE NONClUSTERED COLUMNSTORE INDEX [IX_NON_Emp_ColumnStore]  
2. ON dbo.[Employees](EmpID,EmpName,EmpSalary)  
3. GO   

Advantage

• Columnstore indexes are best for data warehouse, BI etc.
• As far as updating the clustered columnstore index is concerned, you can insert, delete, update or bulk-load data without dropping the index.
  
• Columnstore indexes have all the data highly compressed.