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)
