Sql-server – Adding new columns to Table with Clustured Columnstore Index

What happens when I add another 50 columns to the table?

These columns will be added to the table with NULL values if you have not specified a default value. If you have added a default value, the compressed rowgroups will stay as they are and no updates will be done.

Will they automatically be added to the CCI?

Yes

What should be the best way to handle such conditions?

If you are going to update all the values of the new columns, you will get a lot of compressed row groups with more deleted rows and open delta stores (heaps), when doing a massive update the tuple mover will kick in and turn these delta stores into compressed segments if they are marked as closed (size 1 000 000 rows). To get rid of the compressed segments with only a few rows left you will have to do a REORGANIZE statement

Additional Information

--> You need 1 000 000 rows at least to get CCI to work well, otherwise you will be stuck in your heap delta store. (you only have 400 000)

This will require you to do a rebuild or reorganize of the index to get all rows into a compressed segment

ALTER INDEX idx_cci_target ON cci_target REORGANIZE WITH (COMPRESS_ALL_ROW_GROUPS = ON);

You should also upgrade you SQL Server 2016 to SP1

Try this

Because you do not have so many rows in your Fact table, it might be better to use the NCCI trick. You can create an empty filtered nonclustered columnstore index on your table. This will have a zero cost on your table, but will trigger the batch mode executions plans which will give you a lot of gains for analytical queries.

CREATE NCCI_TEST on TESTABLE (ALL COLUMNS)
WHERE ID = -1 and ID = -2

Examples

CREATE TABLE dbo.test
(
Bla datetime,
blabla varchar(200)
)
GO
CREATE CLUSTERED COLUMNSTORE INDEX CCI_TEST on DBO.TEST
GO

And then insert some rows

INSERT INTO dbo.test
select DATEADD(day, (ABS(CHECKSUM(NEWID())) % 65530), 0),cast(ABS(CHECKSUM(NEWID())) as varchar)
GO 10000

insert into dbo.test
select * from dbo.test
GO 5

If you then go and check your columnstore elements

select * from sys.column_store_row_groups

You see that you now have a delta store, which means this is a heap table without indexes.

If you then rebuild your index with the compress all rowgroups

ALTER INDEX CCI_TEST on DBO.TEST REORGANIZE WITH (COMPRESS_ALL_ROW_GROUPS = ON)

The tombstone elements are the previous deltastore/compressed segments which still linger they will be removed after about 5 minutes

You now have your columnstore compression (which will avoid locking issues)

If we now add a new column

alter table dbo.test add nondefaultvalue varchar(20)

And we check the row groups again

you still have the good layout of your row groups.

If we add another column with a default value and check the rowgroups

alter table dbo.test add defaultvalue varchar(20) not null default('DEFAULT')

If you then however decide to update those values.

UPDATE dbo.test 
set nondefaultvalue = 'DEFAULT'

Things change

We no longer have a compressed segment we can use. If we then try to reorganize this index

ALTER INDEX CCI_TEST on DBO.TEST REORGANIZE

We get our open delta store again, which will cause locking. We can however get it back to a compressed segment by issuing the following command we saw previously

ALTER INDEX CCI_TEST on DBO.TEST REORGANIZE WITH (COMPRESS_ALL_ROW_GROUPS = ON)

Now we have to good columnstore segment again.

So in your case you will have to do a reorganize with compress all row groups, to get it working fine. This should not take long on a table with only 400 000 rows but it is necessary to avoid locking.

If you do bulk inserts the story changes again, but you will have to do 100 000 rows at once to get everything straight into a compressed segment.

Dictionaries

When adding another column a new dictionary will be made for your columnstore index (sort of mapping to the column elements inside your columnstore segments).

select * from sys.column_store_dictionaries

alter table dbo.test add defaultvalue2 varchar(20) not null default('DEFAULT')

select * from sys.column_store_dictionaries

For more information I suggest you go to http://www.nikoport.com/ he has all the extra information you need on columnstore on his blog.