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)
Pareto Charts in Power BI and the DAX behind them
The Pareto principle, commonly referred to as the 80/20 rule, is a concept of prioritisation.
Apr
Databricks: Cluster Configuration
Databricks, a cloud-based platform for data engineering, offers several tools that can be used to
Apr
AI Assistance in Microsoft Fabric
The exponential growth of Large Language Models (LLMs) couples with Microsoft’s close partnership with OpenAI
Apr
10 reasons why it’s worth the effort to understand the value of your data
“If leaders really want to create a data driven culture, the journey starts with them!
Apr
Content Safety in Azure AI Studio
Azure AI Content Safety is a solution designed to identify harmful content, whether generated by
Apr
Model Benchmarks in Azure AI Studio
In the constantly changing field of artificial intelligence (AI) and machine learning (ML), choosing the
Apr
Celebrating International Women’s Day: from Classroom to Code
As we celebrate International Women’s Day, I want to share my journey of breaking stereotypes
Mar
Pretty Power BI – Adding Pagination to Bar Charts
Good User Experience (UX) design is crucial in enabling stakeholders to maximise the insights that
Feb