Clustered columnstore index

As explained in 100 SQL Server Mistakes and How to Avoid Them

A clustered columnstore index is a columnstore index that organizes the table's data itself in columnar storage.

A clustered columnstore index stores a table’s data in columnar form, compressing it so queries read fewer pages. This organization suits data warehouse–style aggregation, especially on fact tables. section 11.11

Mistake: rejecting columnstore indexes because SQL Server 2012 made tables read-only after creation. Modern versions support clustered and nonclustered forms, although advanced and large data types remain unsupported. section 11.11

Fact-table aggregation

Create a clustered columnstore index on an archive or fact table, then compare aggregate-query performance with SET STATISTICS TIME enabled. Add a nonclustered B-tree index when selective lookup needs differ. section 11.11

Figure 11.12 How data is organized in a columnstore index

Use the data-organization figure to contrast columnar pages with conventional row organization. section 11.11

How 100 SQL Server Mistakes and How to Avoid Them covers it

How deep it goes

Explains columnar organization, compression, historical restrictions, current limitations, and complementary B-tree indexing.

Examples

Uses a data-organization figure, an aggregate query, and CREATE INDEX statements for clustered columnstore and nonclustered B-tree indexes.

What you can do after

Evaluate columnstore suitability and create a clustered columnstore index alongside a nonclustered B-tree index.

How it connects

Connects columnstore indexes with fact tables, data warehouses, aggregate queries, and nonclustered B-tree indexes.

liveBookmanning.com

Read 100 SQL Server Mistakes and How to Avoid Them on liveBook Get the book at manning.com

Related concepts

• columnstore indexkind of · A columnstore index is an index that stores and compresses column values in segments to support efficient analytical queries over large row sets.
• clustered indexcontrasts with · A clustered index is a database index whose key determines the physical order of rows in a table or indexed view.

This explainer was generated by Manning's knowledge graph from the text of 100 SQL Server Mistakes and How to Avoid Them. The section links point to the parts of the book it draws on.