Creating a column store index

Problem: How are Column store Indexes created and how does it load the data into an index and what are its limitations.

Solution:CREATE COLUMNSTORE INDEX

If you want to know more about a Column Store Index. Please go through this article. what is a Column Store index?

Starting from SQL Server 2012 and above, you can create a column store indexes. Starting 2014 and above there can be a clustered column store, as well non-clustered column store indexes.

Column Store index:

Convert a row store table to a clustered column store index or create a non-clustered column store index. Use a column store index to efficiently run real-time operational analytics on an OLTP workload or to improve data compression and query performance for data warehousing workloads.

How data is being loaded into a column store clustered index, column store non-clustered index:

when data is inserted, then row groups are created from the table rows, from these row groups, column segments are created. These column segments form into columnstore (compressed column segments).

Below are the images, how the data is processed in the column store indexes.

deltastore
Used with clustered columnstore indexes only, a deltastore is a rowstore table that stores rows until the number of rows is large enough to be moved into the columnstore. A deltastore is used with clustered columnstore indexes to improve performance for loading and other DML operations.

 

Limitations:

You cannot use cursors or triggers on a table with a clustered columnstore index.

The following data types cannot be included in a columnstore index:

ntext, text, and image, rowversion (and timestamp), sql_variant, CLR types (hierarchyid and spatial types), xml, nvarchar(max), varchar(max), and varbinary(max) (Applies to SQL Server 2016 and prior versions, and nonclustered columnstore indexes)

uniqueidentifier (Applies to SQL Server 2012)

Non-Clustered column store indexes cannot be altered, only drop and recreate.

Non-Clustered column store indexes cannot be created on indexed views

Examples:

Example 1: In this example, lets create a non-clustered column store index and see how it compresses the data.

CREATE NONCLUSTERED COLUMNSTORE INDEX IX_CS_Address 

ON dbo.Address 

(AddressPKey, AddressID) 

WITH (DROP_EXISTING =  ON,   -- this command specifies to drop the exisitng indexes on the particular name.

MAXDOP = 2)  --specifies max degree of parallelism to be used while index operations are done.

ON "default"  -- creates index on the default file group

GO 

below is how it looks in the index folder after it gets created.

After creating the non-clustered column index I would like to see how many segments did it create for this particular index.

SELECT i.name, object_name(p.object_id) as Table_name, p.index_id, i.type_desc,  

    COUNT(*) AS number_of_segments 

FROM sys.column_store_segments AS s  

INNER JOIN sys.partitions AS p  

    ON s.hobt_id = p.hobt_id  

INNER JOIN sys.indexes AS i  

    ON p.object_id = i.object_id 

WHERE i.type = 6 

GROUP BY i.name, p.object_id, p.index_id, i.type_desc ; 

GO 

OUTPUT:

name	Table_name	index_id	type_desc	number_of_segments
IX_CS_Address	Address	4	NONCLUSTERED COLUMNSTORE	3