So far, only an unsuccessful insert was tested. Of course, you can also try to insert some valid data. Before that, let's drop the constraint since it is not needed for further explanation:
ALTER TABLE dbo.FactTest DROP CONSTRAINT U_SaleKey; GO
Next, you can insert some valid rows. The following statement inserts 113,990 rows into the test fact table. Note that this is more than the 102,400 row limit for trickle inserts; therefore, you should expect this to be treated as a bulk insert:
INSERT INTO dbo.FactTest (SaleKey, CustomerKey, Customer, CityKey, City, DateKey, StockItemKey, Product, Quantity, TotalAmount, Profit) SELECT 11 * 1000000 + f.[Sale Key] AS SaleKey, cu.[Customer Key] AS CustomerKey, cu.Customer, ci.[City Key] AS CityKey, ci.City, f.[Delivery Date Key] AS DateKey, s.[Stock Item Key] AS StockItemKey, s.[Stock Item] AS Product, f.Quantity, f.[Total Excluding Tax] AS TotalAmount, f.Profit FROM Fact.Sale AS f INNER JOIN Dimension.Customer AS cu ON f.[Customer Key] = cu.[Customer Key] INNER JOIN Dimension.City AS ci ON f.[City Key] = ci.[City Key] INNER JOIN Dimension.[Stock Item] AS s ON f.[Stock Item Key] = s.[Stock Item Key] INNER JOIN Dimension.Date AS d ON f.[Delivery Date Key] = d.Date WHERE f.[Sale Key] % 2 = 0;
You can check whether this was a bulk insert by checking the rowgroups again:
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 343592 0 FactTest 2 3 COMPRESSED 444768 0 FactTest 3 3 COMPRESSED 442874 0 FactTest 4 3 COMPRESSED 113990 0
Although all rowgroups are compressed, you will notice that the last rowgroups have fewer rows than the other rowgroups. It would be more efficient if you could use bulk inserts with more rows, closer to 1,000,000 rows. Now let's try to insert a smaller number of rows. This time, you can also turn on the graphical execution plan:
INSERT INTO dbo.FactTest (SaleKey, CustomerKey, Customer, CityKey, City, DateKey, StockItemKey, Product, Quantity, TotalAmount, Profit) SELECT 12 * 1000000 + f.[Sale Key] AS SaleKey, cu.[Customer Key] AS CustomerKey, cu.Customer, ci.[City Key] AS CityKey, ci.City, f.[Delivery Date Key] AS DateKey, s.[Stock Item Key] AS StockItemKey, s.[Stock Item] AS Product, f.Quantity, f.[Total Excluding Tax] AS TotalAmount, f.Profit FROM Fact.Sale AS f INNER JOIN Dimension.Customer AS cu ON f.[Customer Key] = cu.[Customer Key] INNER JOIN Dimension.City AS ci ON f.[City Key] = ci.[City Key] INNER JOIN Dimension.[Stock Item] AS s ON f.[Stock Item Key] = s.[Stock Item Key] INNER JOIN Dimension.Date AS d ON f.[Delivery Date Key] = d.Date WHERE f.[Sale Key] % 3 = 0;
In the execution, you can see that the insert was a row mode operation. SQL Server does not use batch mode operators for DDL operations. The following screenshot shows a portion of the execution plan, with the Columnstore Index Insert operator highlighted to show that row mode processing was used:
Anyway, let's recheck the status 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;
This time, another open rowgroup is in the result:
table_name row_group_id state state_desc total_rows deleted_rows ---------- ------------ ----- ---------- ---------- ------------ FactTest 0 3 COMPRESSED 1048576 0 FactTest 1 3 COMPRESSED 343592 0 FactTest 2 3 COMPRESSED 444768 0 FactTest 3 3 COMPRESSED 442874 0 FactTest 4 3 COMPRESSED 113990 0 FactTest 5 1 OPEN 75993 0
Let's rebuild the index to get only compressed rowgroups again:
ALTER INDEX CCI_FactTest ON dbo.FactTest REBUILD; GO
After the rebuild, let's see what happened to 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 0
SQL Server has added the rows from the trickle insert to other rowgroups. Let's now select the rows from the last trickle insert:
SELECT * FROM dbo.FactTest WHERE SaleKey >= 12000000 ORDER BY SaleKey;