Let's test what happens when you delete rows from a CCI with the following DELETE command. Before executing the command, you can check the estimated execution plan. Therefore, don't execute the following command yet:

DELETE 
FROM dbo.FactTest 
WHERE SaleKey >= 12000000; 

The following screenshot shows the actual execution plan. You can see that, for the Columnstore Index Delete operator, row mode was used again:

And here is a final check of the state of the rowgroups:

SELECT OBJECT_NAME(object_id) AS table_name, 
 row_group_id, state, state_desc, 
 total_rows, deleted_rows 
FROM sys.dm_db_column_store_row_group_physical_stats 
WHERE object_id = OBJECT_ID(N'dbo.FactTest') 
ORDER BY row_group_id; 

The result shows you that you have only compressed rowgroups:

table_name  row_group_id  state  state_desc  total_rows  deleted_rows
----------  ------------  -----  ----------  ----------  ------------
FactTest               0      3  COMPRESSED     1048576             0
FactTest               1      3  COMPRESSED      428566             0
FactTest               2      3  COMPRESSED      495276             0
FactTest               3      3  COMPRESSED      497375         75993

You can see that one of the rowgroups has deleted rows. Although the total number of rows in this rowgroup did not change, you cannot access the deleted rows; the deleted bitmap B-tree structure for this rowgroup defines which rows are deleted. You can try to retrieve the deleted rows:

SELECT * 
FROM dbo.FactTest 
WHERE SaleKey >= 12000000 
ORDER BY SaleKey; 

This time, no rows are returned.

Finally, let's clean the WideWorldImporters database with the following code:

USE WideWorldImportersDW; 
GO 
DROP TABLE dbo.FactTest; 
GO 

Before finishing this chapter, look at the following table summarizing the space needed for different versions of row and columnar storage: