SQL Server columnstore index update/insert in stored procedure
columnstore, insert-update, sql-server, stored-procedures
Solution
The check is done at compile time, not at execution time. Separate the procedure into it's own, or use dynamic SQL.
But as a general comment this is not the right approach. You should insert into a different table with identical structure, build the columnstore index on this identical table, then use partition switch to replace the old table with the new table: switch out the old table with an empty one, switch in the new table, drop the old data switched out. Similar to the procedure described in How to Update a table with a Columnstore Index. Because of the use of partition switch the users of your table experience a much shorter downtime, as the old table is still online and available during the insert and during the build columnstore stages.
Problem
I was having fun testing out the columnstore index feature of sql server 2012. Because you can't update/insert tables with such indices I read on some options: keep a separate table and use a new partition for every bulk insert or disable the index, perform updates/inserts and then rebuild the index. For my test I chose the latter option and ended up with this stored procedure: ``` -- Disable the columnstore index. ALTER INDEX [All_Columns_Columnstore_Index] ON [dbo].[Tick] DISABLE -- Insert data into tick table from staging table. insert into Tick select [Date], SymbolID, Price from TickTemporary -- Delete data from staging table. delete from TickTemporary -- Enable (rebuild) the columnstore index. ALTER INDEX [All_Columns_Columnstore_Index] ON [dbo].[Tick] REBUILD ``` If I execute these lines manually everything works fine. But if I run the procedure, I get the error that updates/inserts can't be performed on a table that has a columnstore index. Why is this? Update: I followed the advice in the answer I previously accepted but I still get the same thing. ``` -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- Disable the columnstore index. EXEC DisableColumnStoreIndex -- Insert data into tick table from staging table. insert into Tick select [Date], SymbolID, Price from TickTemporary -- Delete data from staging table. delete from TickTemporary -- Enable (rebuild) the columnstore index. EXEC RebuildColumnStoreIndex ``` Even tried placing "begin tran" and "commit tran" around the sproc calls. Using dynamic sql like: ``` declare @sql nvarchar(max) set @sql = 'insert into Tick select [Date], SymbolID, Price from TickTemporary' exec(@sql) ``` works, but really, I want to get by without dynamic sql. Isn't it possible in this case?