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


Clustered columnstore index is given below.

  2. ON dbo.[Employees]


Non-clustered column store index is given below.

  2. ON dbo.[Employees](EmpID,EmpName,EmpSalary)
  3. GO


  • 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.