| Microsoft SQL Server 2012 introduces a new Index type called Columnstore and it offers great performance for data-warehousing loads and other similar queries. Columnstore indexes are designed for data-ware house workloads that are typically read based. This post discuss about how Columnstore index work, how the data is stored in it and what type of queries can benefit from a column store index. It also discusses how to create Columnstore index in SQL Server 2012. |  |
Columnstore Architecture
Traditional indexes (B-trees) in SQL Server stores the data in row based, this model referred as row store. In Columnstore Index all the values from single column are stored contiguously in compressed form. Columnstore index stores each column in separate set of disk page.
Let us take an example to explain the difference with traditional indexes(B-trees)
Employee table which includes ID, Name, City and State columns.
Traditional Indexes stores the data in below format
Columnstore index stores the data on disk page in below format
The major difference is Columnstore index groups and stores data for each column and then joins all the columns to complete the whole index.
Let’s see how this storage model significantly increases the query performance and speed up the retrieval of data.
1. Higher level of compression can be achieved compared to data organised across rows. Columnstore uses Vertipaq compression algorithm. When data is compressed, queries require less IO because the amount of data transferred from disk to memory is significantly reduced.
2. When user runs a query using a Columnstore index, SQL Server fetches data only for columns that required for the query
DataTypes supported by columnstore indexes
- Char and Varchar
- All integer types(int, bigint, smallint and tinyint)
- real and float
- string, money and all date time types
Note: Only one columnstore index can be created per table
If a columnstore index exists, you can read the table but can not update it. INSERT,UPDATE, DELETE and MERGE statements are not allowed on tables using columnstore indexes.
Creating a Columnstore Index
Creating a Columnstore index is similar to creating a traditional SQL SERVER indexes.
All Columnstore indexes must be non-clustered
1. Use SQL Server Management Studio and connect to the SQL Server instance where you want to create the index
2. In Object explorer , Expand database, and expand table in which you would like to create a new Columnstore-Index.
3. Right click on the index folder and choose new index and then click non-clustered columnstore index.
4. Type the name for index and click ok
5. In select columns dialogue box, select the columns to be participate in columnstore index and then click ok.
You can also use the below T-SQL to create the columnstore index
CREATE [ NONCLUSTRED ] COLUMNSTORE INDEX index_name ON
You can force the query to use a columnstore index using WITH statement
example
SELECT DISTINCT (EMPID) FROM dbo.Employee WITH (Non-ClusteredIndexEMPID )
Reference content and pictures from http://rossmistry.com/ and http://blog.datainspirations.com/
| Share this post : |  |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
