SQL SERVER – 2016 – Creating Additional Indexes with Clustered ColumnStore Indexes

Every version of SQL Server brings new capabilities that enhance the features that were introduced in the previous versions. I am sure the team is working based on the feedbacks given by customers as they starting using the new capability. As I was scanning through some of the enhancements that reached this release, I thought some good work has gone behind the usage of ColumnStore Indexes.

So to dissect this enhancement, 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 CloumnStore Index. With SQL Server 2016, I saw that we can create additional B-Tree indexes on top of Clustered ColumnStore Indexes.

As soon I saw this, I thought of creating a script to test the statement on my SQL Server 2016 instance. Let us see how the script flows below:

USE AdventureWorks2016
GO
-- Create New Table
CREATE TABLE [dbo].[MySalesOrderDetail](
[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


Now that we have our base table, let us create the Clustered ColumnStore Index next and add some rows into the table.

-- Create clustered index
CREATE CLUSTERED COLUMNSTORE INDEX [CL_MySalesOrderDetail] ON [dbo].[MySalesOrderDetail] GO
-- Create Sample Data Table
-- WARNING: This Query may run upto 2 minutes based on your systems resources
INSERT INTO [dbo].[MySalesOrderDetail] SELECT S1.*
FROM Sales.SalesOrderDetail S1
GO


Now the moment of truth. Let us create an additional non-clustered Index on top of this table.

CREATE NONCLUSTERED INDEX [IX_MySalesOrderDetail_NormalBTree] ON [MySalesOrderDetail] (UnitPrice, OrderQty, ProductID)
GO


As you can see, this creates the index without any error and it is interesting to see this enhancement come through. I got curious and wanted to create an additional Non-Clustered ColumnStore Index as a test.

-- Error
CREATE NONCLUSTERED COLUMNSTORE INDEX [IX2_MySalesOrderDetail] ON [MySalesOrderDetail] (CarrierTrackingNumber, UnitPriceDiscount)
GO


As expected, this resulted in an error and it was pretty clear too. The error said:

Msg 35339, Level 16, State 1, Line 3
Multiple columnstore indexes are not supported.

With SQL Server 2016, we have got an interesting enhancement to mix and match Clustered ColumnStore Index with B-Tree indexes based on the workload application throws. As I wrap up, let us try to clean up the table structure we just created.
-- Clean up
DROP TABLE IF EXISTS [dbo].[MySalesOrderDetail] GO


Reference: Pinal Dave (https://blog.sqlauthority.com)