Enhanced ColumnStore Indexes in SQL 2014

Available in SQL 2012 & 2014 Enterprise Edition

ColumnStore Indexes were introduced in SQL 2012 and are designed to improve performance in data warehouses. These indexes are good for batch processing and read only queries that use large data sets. Tables that are bulk loaded are good candidates. ColumnStore Indexes stores data in a columnar fashion and uses compression to reduce the amount of IO on disks when used. Each column of data is separated into its own segment. The segments are organized into a row group, which can contain over one million rows. If a table exceeds that amount, SQL will create more row groups and will break the column segments across them. The tuple mover checks every 5 minutes and performs the move if conditions are right.

When saving the index to disk, SQL Server compresses the data in the segments. As a result ColumnStore Indexes can achieve higher compression rates than traditional indexes. Higher compression rates = better performance, since compressed data is a smaller and faster in-memory. According to MS documentation: “Use the ColumnStore Indexes to achieve up to 10x query performance gains over traditional row-oriented storage, and up to 7x data compression over the uncompressed data size.”

As always, your mileage may vary based on values in each column. And keep in mind that more data held in memory also reduces disk IO. So, make sure you have plenty of memory…add if needed. The advantages can be mitigated if you have queries that seek specific data or is updated a lot.

Below is a list of limitations of ColumnStore Indexes. I have highlighted in green how it has been enhanced:

Limitations:

• A table with a ColumnStore Index is now updatable – In the past had to drop and rebuild the index or swap partitions to update
• ColumnStore index creation takes longer than creating a traditional B-tree index (on same columns) due to compression
• Can have only one ColumnStore Index per table so think about including all columns or all frequently used columns
• Cannot have more than 1024 columns
• Can be non-clustered and now expanded to support clustered indexes
• Cannot specify ASC/DESC or INCLUDE clauses
• Expanded to all data types except ntext, text, image, rowversion, sql_variant, (n)varchar(max), varbinary(max), XML, Spatial, CLR
• The definition of a ColumnStore Index cannot be changed with the ALTER INDEX command, must drop and create the index or disable it then rebuild it.  Can use ALTER INDEX command to disable and rebuild.
• Can be created on a table which has compression enabled, but cannot be combined with Page and row compression, and vardecimal storage format
• Cannot be created on view or an indexed view
• Cannot include a sparse column
• Cannot be created on table that uses features: Replication, Change Tracking, Change Data Capture and Filestream

How to create a basic ColumnStore Index:

To create a ColumnStore Index go to Database – tables – indexes – new index – select your type of columnstore index you want to create.

In the new index dialog add your index name and the columns you want.

Or, in TSQL, here is code to do the same:

CREATE TABLE RunningRoutes

(RouteID [int] NOT NULL,

RaceKey [int] NOT NULL,

RaceDateKey [int] NOT NULL,

MileageKey [int] NOT NULL)

GO

CREATE CLUSTERED INDEX cl_simple ON RunningRoutes (RouteID)

GO

CREATE NONCLUSTERED COLUMNSTORE INDEX csindx_runningroutes

ON RunningRoutes

(RaceKey, RaceDateKey, MileageKey)

GO

*************************************************** 

For more information about blog posts, concepts and definitions, further explanations, or questions you may have…please contact us at SQLRxSupport@sqlrx.com. We will be happy to help! Leave a comment and feel free to track back to us. We love to talk tech with anyone in our SQL family!