Hi I am confused about columnstore index, what is column store index and how it is different from clustered and non-clustered 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.
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.
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.
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.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With