What You Should Know About SQL Server 2016 ColumnStore Indexes

One of the exciting features for data warehouse queries in SQL Server 2012 was the columnstore index. These are designed to provide good performance on analytical queries without the need to explicitly specify indexes. There were, however, many restrictions for their use, including:

1. Columnstore indexes had fewer supported datatypes.
2. Columnstore indexes were Non-updatable–once created, the table would become read-only.
3. Columnstore indexes coulnd’t be created with the INCLUDE keyword
4. Any many more (see SQL Server 2012 books online).

Since SQL Server 2012, Microsoft has continued investing in this feature and it’s been getting even better. In this article, I’ll discuss some of the enhancements to Columnstore index in SQL Server 2016.

Clustered columnstore index enhancements in 2016

The clustered columnstore index was introduced in SQL Server 2014. Due to limitations  in SQL Server 2014 that prevented the ability to specify additional indexes, developers have been creating two tables: a normal table with B-tree indexes and a clustered columnstore index. With this solution, keeping both tables synchronized was a challenge.

In SQL Server 2016, this limitation has been removed, and we can have secondary indexes (that is, B-tree style indexes) just like a traditional table. Along with that, these indexes support any number of columns and may be filtered. We also now have the ability to create primary keys and foreign keys by using a B-tree index to enforce these constraints. SQL Server 2016 supports primary keys and foreign keys by using a B-tree index to enforce these constraints on a clustered columnstore index. If we attempt the script below on SQL Server 2014, we would get an error:

CREATE DATABASE CCDemo
GO

USE [CCDemo]
GO

CREATE TABLE [dbo].[MyTable](
	[ID] [int] NULL,
	[FName] [varchar](50) NULL,
	[Lname] [varchar](50) NULL
) ON [PRIMARY]

GO

CREATE CLUSTERED COLUMNSTORE INDEX [CCSI] ON [dbo].[MyTable] WITH (DROP_EXISTING = OFF) 
GO

CREATE NONCLUSTERED INDEX [nc_idx] ON [dbo].[MyTable] (ID ASC)
GO


Msg 35303, Level 16, State 1, Line 42
CREATE INDEX statement failed because a nonclustered index cannot be created on a table that has a clustered columnstore index. Consider replacing the clustered columnstore index with a nonclustered columnstore index.

 

Now, in SQL Server 2016, it would execute just fine and, as shown below, we can see both indexes created:

 

SI and RCSI & ALTER INDEX… REORGANIZE

Starting in SQL Server 2016, the clustered columnstore index supports the snapshot isolation (SI) and read-committed snapshot isolation (RCSI) levels. This allows better concurrency of readers and writers working on the same row. This provides better performance for table which are being written actively. RCSI is a nice feature in which application change is not required and blocking between reader and writer can still be avoided.

On the other hand, to use SI, application code needs to be modified because the default isolation level has to be overridden by snapshot isolation. Columnstore supports index defragmentation by removing deleted rows without the need to explicitly rebuild the index. In SQL Server 2016, ALTER INDEX … REORGANIZE statement can remove deleted rows. It is important to remember that reorganize in an online operation, which would avoid blocking situations if any.

Updatable and filtered non-clustered columnstore indexes

SQL Server 2012 had a capability where non-clustered Columnstore Indexes were allowed but would become read-only snapshots of a normal heap or B-tree table. This would mean the table would become a read-only table. In SQL Server 2014, the clustered columnstore index was supported and the engine supported data modification, but not for a non-clustered index.

In SQL Server 2016, an enhancement was made and the limitation is no more valid.

The good news is that in SQL Server 2016 a table can still have one non-clustered columnstore index, but it will be updatable. Along with this, SQL Server also supports filtered a non-clustered columnstore index. You might be interested to know the benefit of this. Imagine that you know that you only need a well-defined subset of the data; in these situations, a filtered index can reduce the amount of disk space you need. In many cases, filtering can also boost performance. This can be done during index creation.

In-memory columnstore indexes

SQL Server 2016 provides the ability to create a columnstore index on top of a memory-optimized table. The in-memory OLTP feature was introduced in SQL Server 2014 and it allows a complete table to stay in memory all the time. This table doesn’t have traditional F-tree indexes but has completely redesigned storage style and indexes. They provide lock-free and latch-free access to data by using multi version concurrency control (MVCC). There are certain limitations which exist in columnstore index, when used on memory optimized tables:

• No filtered columnstore index supported
• A columnstore index must be defined when the table is created (same as other indexes)
• A columnstore index must include all the columns in the base table (unlike normal tables)

To summarize, all the enhancements made to the columnstore index in SQL Server 2016 have the potential to be very helpful in both business intelligence (BI) and OLTP workloads alike.