Database Reference
In-Depth Information
Chapter 35
Clustered Columnstore Indexes
It is impossible to modify data in tables with nonclustered columnstore indexes. This limitation is one of the major
factors preventing their widespread adoption in SQL Server 2012. SQL Server 2014 addresses this problem by
introducing updatable clustered columnstore indexes (CCI).
This chapter provides an overview of the internal structure of CCI and discusses best practices for data loading
and maintenance of them.
Internal Structure of Clustered Columnstore Indexes
A
clustered columnstore index
is a single instance of the data in a table. Tables with clustered columnstore indexes
cannot have any other indexes defined—neither B-Tree nor nonclustered columnstore indexes.
Clustered columnstore indexes also have several other limitations. You cannot make them
UNIQUE
, nor can you define
triggers on a table. A table cannot have foreign key constraints, nor can foreign keys defined in other tables reference it.
Some other features, such as Replication, Change Tracking, and Change Data Capture are not supported either.
You can create a clustered columnstore index with the
CREATE CLUSTERED COLUMNSTORE INDEX
command. You
do not need to specify any columns in the statement—the index will include all table columns. This adds further
restrictions on the column data types, as we have discussed in the previous chapter.
Internal Structure
Clustered columnstore indexes use the same storage format as nonclustered columnstore indexes, storing
columnstore data in row groups. However, they have two additional elements to support data modifications. The
first is
delete bitmap
, which indicates what rows were deleted from a table. The second structure is
delta store
, which
includes newly inserted rows. Both delta store and delete bitmap use the B-Tree format to store data.
■
SQL Server's use of delete bitmaps and delta stores is transparent to users, which makes the relevant
terminology confusing. You will often see delta stores referenced as another row group in documentation and technical
articles. Moreover, a delete bitmap is often considered a part of a delta store and/or row groups.
Note
To avoid confusion, I will use the following terminology in this chapter. A term
row group
references data stored in a
column-based storage format. I will explicitly reference
delta stores
and
delete bitmaps
as two separate sets of internal
objects as needed.
