Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

What is columnstore index and how is different from clustered and non-clustered

Tags:

sql-server

Hi I am confused about columnstore index, what is column store index and how it is different from clustered and non-clustered index..

like image 276
Dilip Kr Singh Avatar asked Oct 03 '16 09:10

Dilip Kr Singh


People also ask

What is a Columnstore index?

Columnstore index is a new type of index introduced in SQL Server 2012. It is a column-based non-clustered index geared toward increasing query performance for workloads that involve large amounts of data, typically found in data warehouse fact tables.

What are the difference between clustered and a non-clustered index?

A clustered index is used to define the order or to sort the table or arrange the data by alphabetical order just like a dictionary. A non-clustered index collects the data at one place and records at another place.

When should I use Columnstore?

Columnstore indexes work well for mostly read-only queries with large data sets, like data warehousing workloads. Columnstore indexes are not well-suited for queries that seek specific individual values.

What is clustered Columnstore index in Azure SQL data warehouse?

Columnstore index is the preferred technology to run analytics queries in Azure SQL Databases. We recently announced general availability if In-Memory technologies for all Premium databases.


1 Answers

Assume you have a table like below with col1 as primary key

col1  col2  col3
1      2     3
4      5     6

Normal index will be stored like below,assuming a page can hold only one row

   row1  1   2  3--page1-- all columns reside in one page
   row2  4   5  6--page2
   

so when you want to read some thing like sum(col3),SQLServer will need to read page1 and page 2 ,that's a cost of two pages..

Now with column store indexes,The same table will be stored like below

page1  page2   page3
1       2       3
4       5       6

Now if you want to do a sum of col3,it just has to read one page(page3)

Benefit of using column store indexes is, you may touch only necessary pages from Disk .Memory is also efficiently used ,since you will not be storing/reading unwanted data

like image 63
TheGameiswar Avatar answered Oct 04 '22 22:10

TheGameiswar