Comparison
Clustered indexvsClustering key
Clustered index
you pick the primary key and quietly decide the physical order the rows will be stored in on disk forever.
An index whose leaf level is the table itself, so row order on disk follows the key. Range scans on that key become sequential reads, which is enormous; everything else becomes a secondary lookup. Choosing a random UUID as the clustered key scatters inserts across the whole file and is a classic self-inflicted write bottleneck.
Full entry →Clustering key
the table is partitioned by day and sorted within each day by customer, so filtering on a customer skips most blocks inside the partition.
The column data is physically ordered by within a partition, so per-block statistics become selective. It is the second-order layout decision after partitioning, and it is what makes filters on high-cardinality columns cheap without creating a folder per value. Ordering is not free: it is maintained by rewrite, so a clustered table needs periodic maintenance to stay clustered as data is appended.
Full entry →