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)
Databricks Vs Synapse Spark Pools – What, When and Where?
Databricks or Synapse seems to be the question on everyone’s lips, whether its people asking
May
Power BI to Power AI – Part 2
This post is the second part of a blog series on the AI features of
Apr
Geospatial Sample architecture overview
The first blog ‘Part 1 – Introduction to Geospatial data’ gave an overview into geospatial
Apr
Data Lakehouses for Dummies
When we are thinking about data platforms, there are many different services and architectures that
Apr
Enable Smart Facility Management with Azure Digital Twins
Before I started writing this blog, I went to Google and searched for the keywords
Apr
Migrating On-Prem SSIS workload to Azure
Goal of this blog There can be scenario where organization wants to migrate there existing
Mar
Send B2B data with Azure Logic Apps and Enterprise Integration Pack
After creating an integration account that has partners and agreements, we are ready to create
Mar
Incremental Group is acquired by Telefónica Tech
Incremental’s acquisition by Telefónica Tech powers the next phase of growth for the digital technology
Mar