Sizing a Column‑Partitioned Table
Column‑partitioned tables have similar space usage as a table with row partitioning except for the impact of compression.
A column-partitioned table or join index has the potential to incur a large increase in size compared to table that is not column‑partitioned if there are few physical rows in populated combined partitions or if there are many column partitions with ROW format and the subrows are narrow.
A container can only hold values of rows that have the same internal partition number and hash bucket value. The potentially increased size is because of the increased number of physical rows and the overhead of the row header for each physical row. It is far more common for a column-partitioned table to be smaller, and often significantly so, than a table that is not column‑partitioned if COLUMN format is used for narrow column partitions and the table is not over‑partitioned because of the reduced number of physical rows and autocompression.
Use the following general procedure to size a column‑partitioned table.
1 Estimate the size of the table without column partitioning using the standard methods for calculating the size of a table.
2 Adjust your estimate based on the expected impact of autocompression, row header compression, and row header expansion.
You can estimate the necessary adjustment by measuring the impact of your proposed column partitioning scheme on some sample data.