Is columnstore a good option for UPDATES with WHERE clauses? I am developing a database project on SQL Server and I am thinking whether using columnstore index is a good idea.
The project consists of a table (A) that will hold a large number of rows, with many repeated values for a column. Every day, a pack of new rows will be added to the table, with a &quot;DateId&quot; for each pack.
After that, I will need to update a different table (B) joining with A and filtering A for the &quot;DateId&quot; and other columns.
Example in SQL:
CREATE TABLE A (
  [Id] [BIGINT] IDENTITY(1,1) NOT NULL,
  [DateId] [INT] NOT NULL,
  [B_Id] [BIGINT] NOT NULL,
  -- other columns...
  INDEX cci_A CLUSTERED COLUMNSTORE
)

CREATE TABLE B (
  [Id] [BIGINT] IDENTITY(1,1) NOT NULL,
  -- other columns...
  INDEX cci_B CLUSTERED COLUMNSTORE
)

UPDATE B
SET ...
FROM A
INNER JOIN B ON A.B_Id = B.Id
WHERE A.DateId = @myDateId

Is columnstore a good choice in this case?
Modifying a row will cause the old row to be flagged as &quot;deleted&quot; (but its still in the column store index) and the new row to be added to the deltastore (row-based storage which will be compressed when it reaches about 1 million rows). So, as you can imagine, many updates will to some extent degrade your columnstore index over time. You can of course do index maintenance, but a columnstore index on B might not be the best choice...