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.
- CREATE ClUSTERED COLUMNSTORE INDEX [IX_Emp_ColumnStore]
- ON dbo.[Employees]
Syntax
Non-clustered column store index is given below.
- CREATE NONClUSTERED COLUMNSTORE INDEX [IX_NON_Emp_ColumnStore]
- ON dbo.[Employees](EmpID,EmpName,EmpSalary)
- 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.