I was recently working on a project with a developer who needed to execute a stored procedure which followed this pattern (amended for simplicity):
CREATE PROC ExampleProc
AS
IF (SELECT COUNT(*) FROM Sys.indexes WHERE name = ‘NCCIDX_DemoIndex’) > 0
DROP INDEX [NCCIDX_DemoIndex] ON [dbo].[Demo]
INSERT INTO [dbo].[Demo]([Field1], [Field2], [Field3])
VALUES(‘Some’,’Test’,’Data’)
,(‘More’,’Test’,’Data’)
CREATE NONCLUSTERED COLUMNSTORE INDEX [NCCIDX_DemoIndex] ON [dbo].[Demo]
(
[Field1],
[Field2],
[Field3]
)WITH (DROP_EXISTING = OFF)
Unfortunately on execution the procedure would fail intermittently and the following error message would be returned:
Msg 35330, Level 15, State 1, Procedure ExampleProc, Line 7
INSERT statement failed because data cannot be updated in a table with a columnstore index. Consider disabling the columnstore index before issuing the INSERT statement, then rebuilding the columnstore index after INSERT is complete.
The reason for the error message is that, at the time of compilation if the COLUMNSTORE index exists the optimiser decides the INSERT statement will fail (although we drop the index within the procedure this will not be taken into account by the optimiser).
If the procedure is executed and generates a plan at a time when the index does not exist it will run as expected. However we cannot resolve the issue reliably by first removing the index and then creating the procedure. The reason for this is that when the plan is subsequently flushed from the plan cache (something which can be forced by using DBCC FREEPROCCACHE) the optimiser will create a new plan and if the index exists when this happens the query will fail.
The solution I found was to add OPTION(RECOMPILE) to the INSERT statement, this causes it to be assessed at runtime when the index has been dropped stopping the error. Please be aware as this will force recompilation each time the procedure is run adding a small overhead, whilst this was quite acceptable within the DW environment, you will need to make your own assessment.
The amended procedure therefore follows the following format:
CREATE PROC ExampleProc
AS
IF (SELECT COUNT(*) FROM Sys.indexes WHERE name = ‘NCCIDX_DemoIndex’) > 0
DROP INDEX [NCCIDX_DemoIndex] ON [dbo].[Demo]
INSERT INTO [dbo].[Demo]([Field1], [Field2], [Field3])
VALUES(‘Some’,’Test’,’Data’)
,(‘More’,’Test’,’Data’)
OPTION(RECOMPILE)
CREATE NONCLUSTERED COLUMNSTORE INDEX [NCCIDX_DemoIndex] ON [dbo].[Demo]
(
[Field1],
[Field2],
[Field3]
)WITH (DROP_EXISTING = OFF)
Introduction to Data Wrangler in Microsoft Fabric
What is Data Wrangler? A key selling point of Microsoft Fabric is the Data Science
Jul
Autogen Power BI Model in Tabular Editor
In the realm of business intelligence, Power BI has emerged as a powerful tool for
Jul
Microsoft Healthcare Accelerator for Fabric
Microsoft released the Healthcare Data Solutions in Microsoft Fabric in Q1 2024. It was introduced
Jul
Unlock the Power of Colour: Make Your Power BI Reports Pop
Colour is a powerful visual tool that can enhance the appeal and readability of your
Jul
Python vs. PySpark: Navigating Data Analytics in Databricks – Part 2
Part 2: Exploring Advanced Functionalities in Databricks Welcome back to our Databricks journey! In this
May
GPT-4 with Vision vs Custom Vision in Anomaly Detection
Businesses today are generating data at an unprecedented rate. Automated processing of data is essential
May
Exploring DALL·E Capabilities
What is DALL·E? DALL·E is text-to-image generation system developed by OpenAI using deep learning methodologies.
May
Using Copilot Studio to Develop a HR Policy Bot
The next addition to Microsoft’s generative AI and large language model tools is Microsoft Copilot
Apr