As SQL Server users, we are constantly learning and exploring new features and functionalities. However, sometimes our learnings can be questioned, and it is important to revisit and validate the content we have shared. In this blog post, I want to address a recent incident where a reader reached out to me regarding an issue they encountered while working with columnstore indexes.

The reader had tried to run a script from one of my previous blogs, titled “Updating Data in A Columnstore Index,” but it was not working as expected. Concerned about the reliability of my scripts, I immediately tested the script on my own SQL Server 2012 instance and found no issues. I asked the reader to provide me with the error message they received, but to my surprise, they mentioned that there was no error. This piqued my curiosity, and I asked them about the version of SQL Server they were using. They shared that they were running Microsoft SQL Server 2016 (CTP2.1) – 13.0.300.44 (X64). That’s when I realized my mistake.

I had unintentionally overlooked the fact that the script I provided was specific to SQL Server 2012 and did not account for any enhancements made in SQL Server 2016. I immediately accessed my work laptop, which had the SQL Server 2016 CTP installed, and ran the script. As expected, it worked flawlessly. This incident served as a valuable lesson for me, and I am grateful to the reader for bringing it to my attention through the blog.

Now, let’s dive into the script and explore the enhancements made to columnstore indexes in SQL Server 2016. First, we create a new table called “MySalesOrderDetail” with various columns such as SalesOrderID, SalesOrderDetailID, CarrierTrackingNumber, OrderQty, ProductID, and more. We then create a clustered index on the SalesOrderDetailID column.

USE AdventureWorks

-- 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 INDEX [CL_MySalesOrderDetail] ON [dbo].[MySalesOrderDetail] ([SalesOrderDetailID])
GO

-- Create Sample Data Table
INSERT INTO [dbo].[MySalesOrderDetail]
SELECT S1.*
FROM Sales.SalesOrderDetail S1
GO

Next, we move on to the main enhancement in SQL Server 2016 – the ability to create nonclustered columnstore indexes. We create a nonclustered columnstore index on the columns UnitPrice, OrderQty, and ProductID.

-- Create NonClustered ColumnStore Index
CREATE NONCLUSTERED COLUMNSTORE INDEX [IX_MySalesOrderDetail_ColumnStore] ON [MySalesOrderDetail] (UnitPrice, OrderQty, ProductID)
GO

In previous versions of SQL Server, if we attempted to update the table after creating the nonclustered columnstore index, we would encounter an error (35330) stating that the columnstore index needs to be disabled. However, this limitation no longer exists in SQL Server 2016. We can now update the table without any issues.

-- Attempt to Update the table
UPDATE [dbo].[MySalesOrderDetail]
SET OrderQty = OrderQty + 1
WHERE [SalesOrderID] = 43659
GO

This enhancement to columnstore indexes in SQL Server 2016 is a significant improvement. It allows us to update tables with nonclustered columnstore indexes without disabling them first, simplifying our workflow and improving overall performance.

Finally, let’s clean up the demo table we created:

-- Cleanup
DROP INDEX [IX_MySalesOrderDetail_ColumnStore] ON [dbo].[MySalesOrderDetail]
GO

TRUNCATE TABLE dbo.MySalesOrderDetail
GO

DROP TABLE dbo.MySalesOrderDetail
GO

If you have come across any other new enhancements in SQL Server 2016 that you would like me to cover, please let me know. I would be more than happy to explore and blog about them.