SQL Server Hosting – HostForLIFE.eu :: 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.