SQL Server列存储索引更新/在存储过程中插入 [英] SQL Server columnstore index update/insert in stored procedure

问题描述

我很有趣地测试了sql server 2012的列存储索引功能.因为您无法使用此类索引更新/插入表，所以我阅读了一些选项:保留一个单独的表并为每个大容量插入或插入使用新分区禁用索引，执行更新/插入，然后重建索引.

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.

这是为什么?

更新:

我遵循了先前接受的答案中的建议，但我仍然得到同样的东西.

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

甚至尝试在sproc调用周围放置"begin tran"和"commit tran".

Even tried placing "begin tran" and "commit tran" around the sproc calls.

像这样使用动态sql:

Using dynamic sql like:

declare @sql nvarchar(max)
set @sql =
    'insert into Tick
     select [Date],
            SymbolID,
            Price
     from TickTemporary'
exec(@sql)

可以工作，但是实际上，我想在没有动态sql的情况下工作.在这种情况下不可能吗?

works, but really, I want to get by without dynamic sql. Isn't it possible in this case?

推荐答案

检查是在编译时完成的，而不是在执行时完成的.将过程分为自己的过程，或使用动态SQL.

The check is done at compile time, not at execution time. Separate the procedure into it's own, or use dynamic SQL.

但是，一般而言，这不是正确的方法.您应该插入具有相同结构的其他表中，在该相同表上构建columnstore索引，然后使用分区开关将旧表替换为新表:将旧表替换为空表，再转换为新表，掉掉旧数据掉了.与如何使用列存储索引更新表.由于使用了分区切换，表的用户的停机时间要短得多，因为旧表仍处于联机状态，并且在插入过程中以及在构建列存储阶段都可以使用.

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.

这篇关于SQL Server列存储索引更新/在存储过程中插入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！