title: SQL Server columnstore index update/insert in stored procedure
body: <p>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.</p>
  
  <p>For my test I chose the latter option and ended up with this stored procedure:</p>
  
  <pre><code>-- 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
  </code></pre>
  
  <p>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. </p>
  
  <p>Why is this?</p>
  
  <p>Update:</p>
  
  <p>I followed the advice in the answer I previously accepted but I still get the same thing.</p>
  
  <pre><code>-- 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
  </code></pre>
  
  <p>Even tried placing "begin tran" and "commit tran" around the sproc calls.</p>
  
  <p>Using dynamic sql like:</p>
  
  <pre><code>declare @sql nvarchar(max)
  set @sql =
      'insert into Tick
       select [Date],
              SymbolID,
              Price
       from TickTemporary'
  exec(@sql)
  </code></pre>
  
  <p>works, but really, I want to get by without dynamic sql. Isn't it possible in this case?</p>