Sometimes our learnings are questioned and it is important to visit some of my prior blogs to validate the content. This blog is initiated because one of my blog readers who was playing with columnstore index did a lookup to my blog – Updating Data in A Columnstore Index and said the script was not working. Curiously enough, I got worried because this is something I am really paranoid about – the scripts need to run – no matter what.
I had a SQL Server 2012 instance running at my home computer where I cranked up SQL Server Management Studio and immediately tried to run the script. It worked perfectly fine without any problems. I requested the reader to send me the error (if any). He wrote back saying there was no error as described on the other blog. He was expecting an error when updating a non-clustered columnstore index. That it when it struck me – I asked him what version of SQL Server he was running using @@Version. He sent me:
Microsoft SQL Server 2016 (CTP2.1) – 13.0.300.44 (X64) Jun 12 2015 15:56:05 Copyright (c) Microsoft Corporation Enterprise Evaluation Edition (64-bit) on Windows 10 Enterprise 6.3 <X64> (Build 10240: ) (Hypervisor)
I quickly realized my mistake. I was glad he pointed this to me on the blog. I took my work laptop where I have the CTP of SQL Server running. I ran the script and found it is indeed an enhancement to SQL Server 2016.
Let us run the script as described:
USE AdventureWorks
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 INDEX [CL_MySalesOrderDetail] ON [dbo].[MySalesOrderDetail]
( [SalesOrderDetailID])
GO
-- Create Sample Data Table
INSERT INTO [dbo].[MySalesOrderDetail]
SELECT S1.*
FROM Sales.SalesOrderDetail S1
GO
The moment of creating the NonClustered Columnstore Index is next.
-- Create NonClustered ColumnStore Index
CREATE NONCLUSTERED COLUMNSTORE INDEX [IX_MySalesOrderDetail_ColumnStore]
ON [MySalesOrderDetail]
(UnitPrice, OrderQty, ProductID)
GO
As in the previous article, if we try to update the table at this moment – we are supposed to get and Error: 35330 stating the columnstore index needs to be disabled prior to SQL Server 2016. This is no longer encountered.
-- Attempt to Update the table
UPDATE [dbo].[MySalesOrderDetail]
SET OrderQty = OrderQty +1
WHERE [SalesOrderID] = 43659
GO
-- This used to error prior to SQL Server 2016.
-- This is a cool enhancement to ColumnStore Indexes in SQL Server 2016
Now that the learning was cleared, let us go ahead and cleanup the demo table.
-- Cleanup
DROP INDEX [IX_MySalesOrderDetail_ColumnStore] ON [dbo].[MySalesOrderDetail]
GO
TRUNCATE TABLE dbo.MySalesOrderDetail
GO
DROP TABLE dbo.MySalesOrderDetail
GO
Please do let me know if you have seen any new enhancements with SQL Server 2016 that you would like me to cover? Will be more than happy to build one and blog about it.
Reference: Pinal Dave (https://blog.sqlauthority.com)
