SQL Server – Is Columnstore Good for UPDATES with WHERE Clauses

columnstoredatabase-designperformancesql servertable

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 "DateId" for each pack.

After that, I will need to update a different table (B) joining with A and filtering A for the "DateId" 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?

Best Answer

Modifying a row will cause the old row to be flagged as "deleted" (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...

Related Question