ColumnStore Index on Steroids with SQL Server 2016

In a traditional index, also called as B-Tree index, rows are stored in a page and called a row store index. ColumnStore index is a non-conventional index where only one column is stored in a page. This feature was introduced in SQL Server 2012 but is still largely unknown.

According to codeproject.com, in general, there are a few benefits to ColumnStore index:

• Since ColumnStore Index is stored in separate pages, only required pages are fetched from the disk.
• Query processing is faster
• Frequently accessed columns remain in memory
• Enhanced query optimization and execution features improve data warehouse query performance by hundreds to thousands of times in some cases

Since the next version of SQL Server 2016 was announced, I’ve noticed the enhancements to the ColumnStore Indexes have increased. Some of them are nifty and worth a second look for sure. Here are a few enhancements in SQL Server 2016 you should keep in mind. These are some of the top capabilities that were introduced which I will describe here:

Create B-tree indexes with CCI

One of the core limitation before SQL Server 2016 for ColumnStore Indexes was we couldn’t set any additional indexes once a Clustered ColumnStore Index was present. This meant, we needed to drop any existing indexes before we created Clustered ColumnStore Index. With SQL Server 2016, we see that we can create additional B-Tree indexes on top of Clustered ColumnStore Indexes.

Here is the script which fails in SQL Server 2014 but works in SQL Server 2016.

USE AdventureWorks2016
GO
-- Create New Table
CREATE TABLE [dbo].[SalesOrderDetail_Copy](
[SalesOrderID] [int] NOT NULL,
[SalesOrderDetailID] [int] NOT NULL,
[CarrierTrackingNumber] [nvarchar](25) NULL,
[OrderQty] [smallint] NOT NULL,
[ProductID] [int] NOT NULL,
[SpecialOfferID] [int] NOT NULL,
[UnitPrice] [money] NOT NULL,
[UnitPriceDiscount] [money] NOT NULL,
[LineTotal] [numeric](38, 6) NOT NULL,
[rowguid] [uniqueidentifier] NOT NULL,
[ModifiedDate] [datetime] NOT NULL
) ON [PRIMARY]
GO
CREATE CLUSTERED COLUMNSTORE INDEX [CL_MySalesOrderDetail] ON [dbo].[SalesOrderDetail_Copy]
GO
INSERT INTO [dbo].[SalesOrderDetail_Copy] SELECT top 10 S1.*
FROM Sales.SalesOrderDetail S1
GO
CREATE NONCLUSTERED INDEX [IX_MySalesOrderDetail_NormalBTree] ON [SalesOrderDetail_Copy](UnitPrice, OrderQty, ProductID)
GO

Here is the error in SQL Server 2014.

Msg 35303, Level 16, State 1, Line 1

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.

And the same works in SQL Server 2016.

Updateable non-clustered columnstore index (NCCI)

Here is the script to demonstrate this.

USE AdventureWorks2016
GO
-- Create New Table
CREATE TABLE [dbo].[SalesOrderDetail_Copy](
[SalesOrderID] [int] NOT NULL,
[SalesOrderDetailID] [int] NOT NULL,
[CarrierTrackingNumber] [nvarchar](25) NULL,
[OrderQty] [smallint] NOT NULL,
[ProductID] [int] NOT NULL,
[SpecialOfferID] [int] NOT NULL,
[UnitPrice] [money] NOT NULL,
[UnitPriceDiscount] [money] NOT NULL,
[LineTotal] [numeric](38, 6) NOT NULL,
[rowguid] [uniqueidentifier] NOT NULL,
[ModifiedDate] [datetime] NOT NULL
) ON [PRIMARY]
GO
USE [AdventureWorks2016]
GO
CREATE NONCLUSTERED COLUMNSTORE INDEX [NCCI_SalesOrderID] ON [dbo].[SalesOrderDetail_Copy]
(
[SalesOrderID]
)
GO
INSERT INTO [dbo].[SalesOrderDetail_Copy] SELECT top 10 S1.*
FROM Sales.SalesOrderDetail S1
GO

Here is the error in SQL Server 2014.

Msg 35330, Level 15, State 1, Line 32

INSERT statement failed because data cannot be updated in a table that has a nonclustered columnstore index. Consider disabling the columnstore index before issuing the INSERT statement, and then rebuilding the columnstore index after INSERT has completed.

And we can see that it works in SQL Server 2016 ((10 row(s) affected))

ColumnStore index on memory-optimized tables

Here is the script

USE [master]
GO
CREATE DATABASE [IMO_DB]
GO
USE [master]
GO
ALTER DATABASE [IMO_DB] ADD FILEGROUP [IMO_FG] CONTAINS MEMORY_OPTIMIZED_DATA
GO
ALTER DATABASE [IMO_DB] ADD FILE ( NAME = N'IMO_FG', FILENAME = N'C:\IMO_FG' ) TO FILEGROUP [IMO_FG]
GO
USE [IMO_DB]
GO
CREATE TABLE dbo.sample_memoryoptimizedtable
(
c1 int NOT NULL,
c2 float NOT NULL,
c3 decimal(10,2) NOT NULL,
CONSTRAINT PK_sample_memoryoptimizedtable PRIMARY KEY NONCLUSTERED (c1),
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA)
GO
USE [IMO_DB]
GO
ALTER TABLE [sample_memoryoptimizedtable] ADD INDEX IDX_CC CLUSTERED COLUMNSTORE

Here is the error in SQL Server 2014 which would come when last statement is executed.

Msg 102, Level 15, State 1, Line 25

Incorrect syntax near ‘COLUMNSTORE’.

As its added as new feature, it works in SQL Server 2016.

Here are a few other updated capabilities:

• Parallel data import from staging table.
• Online defragmentation of columnstore indexes
• Ability to access NCCI and CCI using snapshot based isolation levels (RCSI and SI).

In summary, we can see that many use cases have been take care by Microsoft. I am sure Microsoft team is working based on the feedback given by customers as they starting using the new capability.

Datavail Script: Terms & Conditions

By using this Oracle upgrade software script (“Script”), you are agreeing to the following terms and condition, as a legally enforceable contract, with Datavail Corporation (“Datavail”). If you do not agree with these terms, do not download or otherwise use the Script. You (which includes any entity whom you represent or for whom you use the Script) and Datavail agree as follows:

1. CONSIDERATION. As you are aware, you did not pay a fee to Datavail for the license to the Script. Consequently, your consideration for use of the Script is your agreement to these terms, including the various waivers, releases and limitations of your rights and Datavail’s liabilities, as set forth herein.

2. LICENSE. Subject to the terms herein, the Script is provided to you as a non-exclusive, revocable license to use internally and not to transfer, sub-license, copy, or create derivative works from the Script, not to use the Script in a service bureau and not to disclose the Script to any third parties. No title or other ownership of the Script (or intellectual property rights therein) is assigned to you.

3. USE AT YOUR OWN RISK; DISCLAIMER OF WARRANTIES. You agree that your use of the Script and any impacts on your software, databases, systems, networks or other property or services are solely and exclusively at your own risk. Datavail does not make any warranties, and hereby expressly disclaims any and all warranties, implied or express, including without limitation, the following: (1) performance of or results from the Script, (2) compatibility with any other software or hardware, (3) non-infringement or violation of third party’s intellectual property or other property rights, (4) fitness for a particular purpose, or (5) merchantability.

4. LIMITATION ON LIABILITY; RELEASE. DATAVAIL SHALL HAVE NO, AND YOU WAIVE ANY, LIABILITY OR DAMAGES UNDER THIS AGREEMENT.

You hereby release Datavail from any claims, causes of action, losses, damages, costs and expenses resulting from your downloading or other use of the Script.

5. AGREEMENT. These terms and conditions constitute your complete and exclusive legal agreement between you and Datavail.