Deleting from SQL Server ColumnStore gives "Cannot insert duplicate key" error

1
votes

Our product loads data from CSV files into various tables via the .NET System.Data.SqlClient.SqlBulkCopy class. After a successful load, all data older than a certain timestamp is removed from the database. This is done by executing the following command until no rows are deleted:

DELETE TOP (100000) FROM DBO.TBL_DDBB WHERE MeasureDateTime < CAST('2020/04/05 00:00:00' as datetime)

This works fine from tables in RowStore format, but when executing this command on a table with a ColumnStore index we get the following error:

Cannot insert duplicate key row in object 'dbo.deleted_bitmap' with unique index 'tuple_key'. The duplicate key value is (98, 0).The statement has been terminated.

The problem is reproducible on multiple tables in ColumnStore format on this SQL Server machine, but not on other SQL Servers that we have.

I cannot figure out why this happens, and I can't anything on the web mentioning this error. Has anybody seen this before or can anybody tell me how to prevent it?


Version information on the SQL Server used:

Microsoft SQL Server 2016 (SP2-CU12) (KB4536648) - 13.0.5698.0 (X64)
Feb 15 2020 01:47:30 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows Server 2012 R2 Standard 6.3 (Build 9600: ) (Hypervisor)

1
sql-serversql-server-2016sql-deletecolumnstore
Do you have triggers on the tables recording information from deleted rows? – AlwaysLearning
Nope; no triggers, no constraints, not even secondary indexes. I think the 'dbo.deleted_bitmap' object the error mentions is a SQL Server construct for dealing with row-deletion in ColumnStores. It's certainly not one of ours. – Ide
See if the row groups containing the data being deleted are closed. Since the delete works on other servers, it could be the difference is due to open versus closed. – Dan Guzman
Hi Dan. Good suggestion, but I checked and unfortunately this is not the case. – Ide

1 Answers

1
votes

columnstore : SQL Server marks the row as logically deleted, but doesn't reclaim the physical storage for the row until the index is rebuilt.

So logically till you rebuild index data is there in index.

After delete you have to rebuild index to reclaim key and space.

or ALTER INDEX ... REORGANIZE

More details on Msdn link

how delete work in column store good page deleted_bitmap