Skip to main content

Posts

Showing posts from December, 2018

Column-Store index in SQL Server

Column-Store Indexes
Column-Store index is designed for improving query performance which is used for heavy amount of data like data warehouse fact tables. This type of index stores the index data in column based rather than row based. This index is introduced in version SQL server 2012; basically this is column based non-clustered index.
Why should we use Column-Store Index Column-Store index provides a very high level of compression, typically by 10 times, to significantly reduce your data warehouse storage cost. For analytics, a column-store index offers an order of magnitude better performance than a B-Tree index. Columns store values from the same domain and commonly have similar values, which result in high compression rates. I/O bottlenecks in your system are minimized or eliminated, and memory footprint is reduced significantly. High compression rates improve query performance by using a smaller in-memory footprint. In turn, query performance can improve because SQL Server can p…

Non-clustered index in SQL Server

Non-clustered index A non-clustered index has a structure separate from data row, it means non-clustered order of the row does not match the physical order of the actual data. If we talk about the B-TREE structure, in the non-clustered index, the leaf pages of the index do not contain any actual data, but instead, contain pointers to the actual data. These pointers would point to the clustered index data page where the actual data exists (or the heap page if no clustered index exists on the table).
Non-clustered indexes (also called “indexes”) are the normal indexes that are created on your tables. SQL Server supports 999 non-clustered indexes per table.
Each non-clustered index can contain up to 1023 included columns. Columns of the TEXT, NTEXT and IMAGE data types cannot be included either.
There are more things that could be put in here, but this will get you a good start. If you think of anything that I’ve missed please feel free to post them below.
Benefits of non-clustered index A non…