Every version of SQL Server brings new capabilities that enhance the features that were introduced in the previous versions. The SQL Server development team takes customer feedback into consideration when working on these new capabilities. One of the notable enhancements in SQL Server 2016 is the usage of ColumnStore Indexes.

Prior to SQL Server 2016, there was a limitation with ColumnStore Indexes – it was not possible to set any additional indexes once a Clustered ColumnStore Index was present. This meant that existing indexes had to be dropped before creating a Clustered ColumnStore Index. However, with SQL Server 2016, this limitation has been addressed and it is now possible to create additional B-Tree indexes on top of Clustered ColumnStore Indexes.

Let’s take a look at a script that demonstrates this enhancement:

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

-- Create clustered index
CREATE CLUSTERED COLUMNSTORE INDEX [CL_MySalesOrderDetail] ON [dbo].[MySalesOrderDetail]
GO

-- Create Sample Data Table
-- WARNING: This Query may run up to 2 minutes based on your system's resources
INSERT INTO [dbo].[MySalesOrderDetail]
SELECT S1.*
FROM Sales.SalesOrderDetail S1
GO

-- Create an additional non-clustered Index
CREATE NONCLUSTERED INDEX [IX_MySalesOrderDetail_NormalBTree] ON [MySalesOrderDetail] (UnitPrice, OrderQty, ProductID)
GO

-- Attempt to create a Non-Clustered ColumnStore Index (will result in an error)
CREATE NONCLUSTERED COLUMNSTORE INDEX [IX2_MySalesOrderDetail] ON [MySalesOrderDetail] (CarrierTrackingNumber, UnitPriceDiscount)
GO

-- Clean up
DROP TABLE IF EXISTS [dbo].[MySalesOrderDetail]
GO

As you can see from the script, it is now possible to create additional non-clustered indexes on top of a Clustered ColumnStore Index without any errors. This enhancement allows for more flexibility in optimizing the performance of your SQL Server queries based on the specific workload.

However, it is important to note that creating multiple ColumnStore Indexes is still not supported. If you attempt to create a Non-Clustered ColumnStore Index on top of an existing Clustered ColumnStore Index, you will receive an error message.

In conclusion, SQL Server 2016 introduces the ability to mix and match Clustered ColumnStore Indexes with B-Tree indexes, providing more options for optimizing query performance. This enhancement is a result of the continuous improvement efforts by the SQL Server development team based on customer feedback.