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:
Storage |
Rows |
Reserved |
Data |
Index |
CI |
227,981 |
49,672 KB |
48,528 KB |
200 KB |
CI row compression |
227,981 |
25,864 KB |
24,944 KB |
80 KB |
CI page compression |
227,981 |
18,888 KB |
18,048 KB |
80 KB |
CI (10 times more rows) |
2,279,810 |
502,152 KB |
498,528 KB |
2,072 KB |
CI with NCCI |
2,279,810 |
529,680 KB |
498,528 KB |
29,432 KB |
CCI |
2,279,810 |
23,560 KB |
23,392 KB |
0 KB |
CCI archive compression |
2,279,810 |
19,528 KB |
19,336 KB |
0 KB |
CCI archive compression and NCI |
2,279,810 |
90,256 KB |
19,344 KB |
70,192 KB |