Database Reference
In-Depth Information
,IDs(ID) as (select row_number() over (order by (select NULL)) from N6)
insert into dbo.CCI(Col1,Col2)
select ID, 'aaa'
from IDS
go
create clustered columnstore index IDX_CS_CLUST on dbo.CCI
with (maxdop=1)
go
select g.state_description, g.row_group_id, s.column_id
,s.row_count, s.min_data_id, s.max_data_id, g.deleted_rows
from
sys.column_store_segments s join sys.partitions p on
s.partition_id = p.partition_id
join sys.column_store_row_groups g on
p.object_id = g.object_id and
s.segment_id = g.row_group_id
where
p.object_id = object_id(N'dbo.CCI')
order by
g.row_group_id, s.column_id;
Figure 35-4 shows the output from sys.column_store_segments and sys.column_store_row_groups views. The
columnstore index has two row groups and does not have delta store nor delete bitmap. You can see Col1 values that
are stored in both row groups in min_data_id and max_data_id columns for the rows that have column_id=1 .
Figure 35-4. Delta store and Delete bitmap: Sys.column_store_segments and sys.column_store_row_groups output
In the next step, shown in Listing 35-2, we will perform some data modifications in the table. The first statement
inserts two new rows into the table. The second statement deletes three rows, including one of the rows that we just
inserted. Finally, we will update another newly inserted row.
Listing 35-2. Delta store and Delete bitmap: Data modifications
insert into dbo.CCI(Col1,Col2)
values
(2000000,REPLICATE('c',4000)),
(2000001,REPLICATE('d',4000));
delete from dbo.CCI
where Col1 in
(
100 -- Row group 0
,16150 -- Row group 1
,2000000 -- Newly inserted row (Delta Store)
);
 
Search WWH ::




Custom Search