Columnstore index

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

A columnstore index is an index that stores and compresses column values in segments to support efficient analytical queries over large row sets.

A columnstore index stores collections of column values on pages rather than collections of complete rows. It compresses column values and divides the index into segments, making it suited to analytical queries that read large sets of data rather than individual rows. section 11.11

Mistake #77: treating every index as a B-tree. Traditional indexes organize rows through a B-tree structure; a columnstore pivots that layout around columns. Each segment holds 102,400 to 1,048,576 rows and carries metadata that can let the query engine skip segments unrelated to a query. section 11.11

Figure 11.12 How data is organized in a columnstore index

Compare the row-oriented and column-oriented layouts before choosing an index strategy. section 11.11

Archive-table aggregate

An aggregate over dbo.ImpressionsArchive, such as AVG(BidPrice) and AVG(CostPerMille), represents the analytical workload where a columnstore index can offer major gains. Create a clustered columnstore index on the archive table, then measure the query rather than assuming the result. CREATE CLUSTERED COLUMNSTORE INDEX ImpressionsArchiveCCSI ON dbo.ImpressionsArchive; section 11.11

Use this practical rule set:

• Consider a columnstore index for large fact tables and data warehouse-style analytical queries. section 11.12
• Use segment metadata as the reason it can avoid reading irrelevant portions of large data sets. section 11.11
• Keep workload fit central: columnstore indexes are presented as an underused analytical option, not merely as another conventional B-tree index. section 11.11
• Add a nonclustered B-tree index separately when the table design calls for both index types. section 11.11

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

How deep it goes

Explains column-oriented pages, segment sizes, metadata, and segment skipping without detailing compression algorithms.

Examples

Uses ImpressionsArchive aggregate queries and clustered columnstore plus nonclustered B-tree creation statements.

What it assumes

Teaches the row-versus-column storage distinction through comparison with traditional B-tree indexes.

What you can do after

Apply it via SQL Server CREATE CLUSTERED COLUMNSTORE INDEX statements for analytical archive tables.

How it connects

Connects columnstore indexes to B-tree indexes, clustered indexes, nonclustered indexes, fact tables, and analytical queries.

liveBookmanning.com

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

Related concepts

• indexingkind of · Indexing is the organization or use of positions, keys, or lookup structures to identify and retrieve selected data or elements.
• segmenthas part · A segment is a distinct portion or bounded region of a larger structure, system, or sequence, with boundaries or properties determined by its context.
• clustered columnstore indexincludes · A clustered columnstore index is a columnstore index that organizes the table's data itself in columnar storage.
• B-treealternative to · A B-tree is a balanced multiway search tree that maintains ordered keys and supports efficient searching, insertion, deletion, and sequential access.

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.