Introduction to Columnstore Indexes - Pro SQL Server Internals - page 708

on [FG2014]
go

/*** Step 3: Switching a staging table as the new partition of the main table ***/
alter table dbo.StagingTable
switch to dbo.FactTable
partition 5;

■ Tip You can use a partitioned view that combines data from updatable tables and read-only tables with columnstore

indexes.

Finally, SQL Server 2014 introduces updateable clustered columnstore indexes, which we will discuss in the next chapter.

Data Storage
Each data column in columnstore indexes is stored separately in a set of structures called row groups . Each row group
stores data for up to approximately one million or, to be precise, 2^20=1,048,576 rows. SQL Server tries to populate
row groups completely during index creation, leaving the last row group partially populated. For example, if table has
five million rows, SQL Server creates four row groups of 1,048,576 rows each and one row group with 805,696 rows.

■ In practice, you can have more than one partially populated row group when multiple threads create
columnstore indexes using a parallel execution plan. each thread will work with its own subset of data, creating
separate row groups in this scenario. moreover, in the case of partitioned tables, each table partition has its own set
of row groups.

Note

After row groups are built, SQL Server combines all column data on a per-row group basis and encodes and
compresses them. The rows within a row group can be rearranged if that helps to achieve a better compression rate.
Column data within a row group is called a segment . SQL Server loads an entire segment to memory when it
needs to access columnstore data.
Figure 34-15 illustrates the index creation process. It shows a columnstore index with four columns and three row
groups. Two row groups are populated in full and the last one is partially populated.