hope you can give me some pointers on this behavior

I have a NC Columnstore and in the same batch I want to drop this column store and "delete" some information.

But I get this error: DELETE statement failed because data cannot be updated in a table with a columnstore index. Consider disabling the columnstore index before issuing the DELETE statement, then rebuilding the columnstore index after DELETE is complete.

For what I can image this is due to the compilation throwing the error.

  • Why do these two operations have to happen in the context of the same batch? An easy way to get around logical compilation errors like this is to execute the delete in dynamic SQL or from a separate stored procedure. – Aaron Bertrand Apr 10 '15 at 23:32
  • Because business logic (best way I can describe why). – Enrique Apr 10 '15 at 23:35
  • 2
    Well, you need your code to compile, so sometimes business logic "reasons" will have to take a back seat. There is no magic here. – Aaron Bertrand Apr 11 '15 at 0:16

In the end i used the OPTION(RECOMPILE) at the end it worked for the scenario I was facing, but bear in mind that this won't work for parameters.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.