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)

Previous Post
SQL SERVER – FIX: Msg 4335, Level 16, State 1 – The specified STOPAT time is too early
Next Post
SQL SERVER – Script: Change Service Account Using WMI / SMO

Related Posts

2 Comments. Leave new

  • Hi … is there a version that works on sql 2014 (standard) and not enterprise for column clustered indexes? If not… does the standard version of sql 2016 support them? These are intriguing but seem limited to enterprise.

    Reply
    • I don’t think I can answer on Microsoft’s behalf of why certain features are only available in Enterprise Edition. I am sure these constraints remain even in SQL Server 2016. Most of the top features are Enterprise Edition features only and Columnstore indexes will be no exception.

      Reply

Leave a Reply

Menu