What is a ColumnStore Index?
A columnstore index is a technology for storing, retrieving and managing data by using a columnar data format, called a columnstore.
In a columnar, all the column 1 values are physically together, followed by all the column 2 values
How Different are Column and Row Store?
In row store data are stored in the disk tuple by tuple.
Where in column store data are stored in the disk column by column.
Most of the queries does not process all the attributes of a particular relation.
For example the query
Select c.name, c.address
From CUSTOMES as c
Where c.region=‘Mumbai’;
Only process three attributes of the relation CUSTOMER. But the customer relation can have more than three attributes.
Column-stores are more I/O efficient for read-only queries as they read, only those attributes which are accessed by a query.
Benefits of Column Store Index:
A columnstore index is a technology for storing, retrieving and managing data by using a columnar data format, called a columnstore.
In a columnar, all the column 1 values are physically together, followed by all the column 2 values
How Different are Column and Row Store?
In row store data are stored in the disk tuple by tuple.
Where in column store data are stored in the disk column by column.
Most of the queries does not process all the attributes of a particular relation.
For example the query
Select c.name, c.address
From CUSTOMES as c
Where c.region=‘Mumbai’;
Only process three attributes of the relation CUSTOMER. But the customer relation can have more than three attributes.
Column-stores are more I/O efficient for read-only queries as they read, only those attributes which are accessed by a query.
Benefits of Column Store Index:
- Each page stores data only on basis of column. That give significant improve in performance when fetching selected columns from table
- One page one column data, increase the chances of high percentage of data compression because of similar data type & data for same column
- Does not physically store columns in a sorted order. Instead, it stores data to improve compression and performance
- Column Store Index by default compressed data by New Column Store Compression
- Cluster ColumnStore index cannot be combined with other index types, you can use Non-Cluster index to combine it with other indexes
- Only Clustered Column index are updatable on the other side Non-Clustered ColumnStore index are read only
- Non-Clustered CloumnStore Index requires extra storage space for column copy in index
- ColumnStore index creation takes more time (1.5 times almost) than creating a B-tree index (on same set of columns) because the data is compressed
- A table can have only one ColumnStore Index and hence you should consider including all columns or at least all those frequently used columns of the table in the index
- A ColumnStore Index can only be non cluster and non unique index; you cannot specify ASC/DESC or INCLUDE clauses
- Not all data types (binary, varbinary, image, text, ntext, varchar(max), nvarchar(max), etc.) are supported
- The definition of a ColumnStore Index cannot be changed with the ALTER INDEX command, you need to drop and create the index or disable it then rebuild it
- A ColumnStore Index cannot be created on view
- SQL Server 2014 supports Clustered ColumnStore Index whereas SQL Server 2012 supports only Non-Clustered ColumnStore Indexes
- In SQL Server 2012, As soon as you had created a ColumnStore Index on a table, the underlying table was read only, and no changes to the data were allowed. But SQL Server 2014 allowed to have updatable ColumnStore Index
- Deltastore used with clustered ColumnStore indexes only, to gain performance.
Refer below videos for more details.
0 comments: