Column Store Indexes

Column Store Indexes

A column store index organizes the data in individual columns that are joined together to form the index. This new type of index stores data column-wise instead of row-wise. When you add Column Store index the Table becomes “Read only”.  So the only solution is to Drop and Recreate the Indexes.

Column vs. Row Index

Row Index

The data is stored in two pages i.e. “page1” and “page2”. “Page1” has two rows and “page2” also has two rows. Now if you want to fetch only “column1”, you have to pull records from two pages i.e. “Page1” and “Page2”.

Capture

Column Index

If somehow we can store data column wise we can avoid fetching data from multiple pages. That’s what column store indexes do. We now need to fetch “column1” data only from one page rather than querying multiple pages.

Capture

CONCLUSION

So conclusion is that Column Store Indexes stores data in a column manner and are used for Fast Access Data as compared to Row Index.

Tags:
  • Column Store,
  • Column Store Indexes,
  • SQL Server 2012,
  • SQL Update,
  • Store Indexes,
  • Leave a Reply