Partitioning, columnstore and compression

Large-data design starts with the workload.

UnderstandStudy an examplePractise

Understand the concept

Partitioning divides data into key ranges. Date predicates can enable partition elimination, but adding partitions does not inherently speed up queries. Plan aligned indexes, boundaries and switching operations. Partitioning is not itself an archive or backup strategy.

Columnstore supports large analytical scans through column-oriented storage and compression. Rowgroup quality, segment elimination, delta stores and batch processing matter. Rowstore can suit short point lookups. PAGE and ROW compression trade CPU against IO. A twelve-order lab cannot model warehouse performance; the example index is temporary. Production design requires workload, volume, version and maintenance measurements.

WHEN TO USE IT

Choose storage for analytical workloads.

COMMON PITFALL

Expecting columnstore to accelerate every query on a tiny OLTP table.

01 / PRACTICE

One topic. Three levels of depth.

T-SQL / SQL Server 2019+

SELECT name, type_desc FROM sys.indexes WHERE object_id = OBJECT_ID(N'lab.OrderItem');

Inspect current storage types.

This page does not execute SQL. Practise in the downloadable SqlStudioLab using SSMS. Before rerunning temporary-table examples, remove only the relevant #table. Views, procedures and functions create persistent lab objects; never use a production connection.

02 / YOUR TURN

Try it yourself first.

Inspect partition row counts for Product.

Show the solution and explanation

Worked solution

SELECT partition_number, rows FROM sys.partitions WHERE object_id = OBJECT_ID(N'lab.Product') AND index_id IN (0, 1);

Selecting base storage avoids counting nonclustered index rows twice.

RasimAtaberkŞahin

Understand the concept

One topic. Three levels of depth.

Try it yourself first.