當您需要游標並且擁有列儲存聚集索引時該怎麼辦

我有一個非常大的帶有 ColumnStore 聚集索引的 SQL Server 表。該表的查詢速度非常快，所以這很好。但是我需要對錶中的大部分記錄進行一些更新。當我嘗試傳統的更新語句時，我的事務日誌填滿了。通常我會通過使用游標來使用複合鍵的附加部分來處理這個問題，這樣我就可以更新批次並在循環中執行更新語句。但是，我剛剛通過錯誤消息了解到，這不適用於具有聚集索引的 ColumnStore 表。

有人可以建議更新數據的替代方法嗎？

我應該只關閉事務日誌嗎？這讓我很擔心。

我不能做的一件事是為我的事務日誌添加更多空間。它已經相當大了。

declare @ig as cursor;
declare @pname varchar(200)

set @ig = cursor for 
select distinct injection_group from mytable where 
ProductGroup = 'Widget'
and target_month = '1/1/2018'
open @ig;

fetch next from @ig into @pname;

while @@FETCH_STATUS = 0
begin
   print @pname;
   update 
   mytable
   set injection_group = replace(injection_group, '  ', ' ')
   where productgroup = 'Widget'
   and target_month = '1/1/2018'
   and injection_group = @pname
   fetch next from @ig into @pname;
end

close @ig;
deallocate @ig;

消息 35370，級別 16，狀態 1，行 5 游標在具有聚集列儲存索引的表上不受支持。

這個答案是從SQL Server 2016的角度寫的，但是相信大部分重要的細節都是一樣的。

從事務日誌的角度來看，更新非常昂貴。它們在幕後實現為刪除+插入。刪除每行具有固定的事務日誌成本。如果我沒記錯的話，每刪除 100 萬行大約需要 200 MB。插入只能去增量儲存，所以數據被寫成堆。這些堆在 2016 年未壓縮，在 2014 年進行了頁面壓縮。

最好的做法是刪除 + 插入而不是更新列儲存索引。如果您真的在處理表中的大部分行，您可以將所有新數據插入到新副本中，並在完成後切換錶。幾乎所有插入的行都將繞過增量儲存並寫入壓縮行組。與寫入增量儲存相比，將數據寫入壓縮行組所需的事務寫入要少得多。

在 SQL Server 2016 中，您可以使用這種方法顯著提高性能。根據 Niko 的文章，SQL Server 2014 也是如此。

DECLARE
@LargestKeyProcessed varchar(200) = 'A',
@NextBatchMax varchar(200),
@RC INT = 1;

WHILE (@RC > 0)
BEGIN

SELECT distinct TOP (1) @NextBatchMax = injection_group
FROM mytable where 
productgroup = 'Widget'
and target_month = '1/1/2018'  
and injection_group > @LargestKeyProcessed
ORDER BY injection_group ASC;

print @NextBatchMax;

update 
mytable
set injection_group = replace(injection_group, '  ', ' ')
where productgroup = 'Widget'
and target_month = '1/1/2018'
and injection_group = @NextBatchMax

SET @RC = @@ROWCOUNT;
SET @LargestKeyProcessed = @NextBatchMax;

END

引用自：https://dba.stackexchange.com/questions/192479