I have a very large SQL Server table with a ColumnStore clustered index. The table is very fast to query, so this is good. However I need to do some updates to a large portion of records in the table. When I tried a conventional update statement, my transaction log filled up. Normally I would handle this by using a cursor to use an additional part of the composite key so I can update batches and run the update statements in a loop. However I have just learned via the error message that this won't work on a ColumnStore table with a clustered index.
Can someone please suggest an alternative method for updating the data?
Should I just turn transaction logging off? That worries me.
One thing I can't do is add more space to my transaction log. It's already quite large.
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;
Msg 35370, Level 16, State 1, Line 5 Cursors are not supported on a table which has a clustered columnstore index.