Table and Column Storage
The information on calculated storage of data tables and columns are accessible from
the system views below. These views give the database administrator insight in the
actual footprint of the persistent tables and the maximum playground used when
indices are introduced upon them. The actual storage footprint of an existing
database can be obtained by querying the views sys.schemastorage or sys.tablestorage
or sys.storage (for most detailed information). It represents the actual state of
affairs, i.e. storage of files on disk of columns and foreign key indices, and
possible temporary hash indices.
For strings we take a sample to determine their average length.
sys.schemastorage
| name | type | references | description |
|---|
| "schema" | VARCHAR | sys.schemas.name | The schema name. |
| "storages" | BIGINT | | The total number of storage files used by all tables in the schema. |
| "columnsize" | BIGINT | | The total size in bytes of all the column data of all tables in the schema. |
| "heapsize" | BIGINT | | The total size in bytes of all the column heap data of all tables in the schema. |
| "hashsize" | BIGINT | | The total size in bytes of all the column hash data of all tables in the schema. |
| "imprintsize" | BIGINT | | The total size in bytes of all the column imprints data of all tables in the schema. |
| "orderidxsize" | BIGINT | | The total size in bytes of all the ordered column indices data of all tables in the schema. |
sys.tablestorage
| name | type | references | description |
|---|
| "schema" | VARCHAR | sys.schemas.name | The schema name. |
| "table" | VARCHAR | sys.tables.name | The table name. |
| "rowcount" | BIGINT | | The number of rows in the table. |
| "storages" | BIGINT | | The number of storage files used by the table. Besides column storage files also indices and dictionary heaps and pkey hashes require a storage file. |
| "columnsize" | BIGINT | | The total size in bytes of all the column data of the table. |
| "heapsize" | BIGINT | | The total size in bytes of all the column heap data of the table. |
| "hashsize" | BIGINT | | The total size in bytes of all the column hash data of the table. |
| "imprintsize" | BIGINT | | The total size in bytes of all the column imprints data of the table. |
| "orderidxsize" | BIGINT | | The total size in bytes of all the ordered column indices data of the table. |
sys.storage
| name | type | references | description |
|---|
| "schema" | VARCHAR | sys.schemas.name | The schema name. |
| "table" | VARCHAR | sys.tables.name | The table name. |
| "column" | VARCHAR | sys.columns.name | The column name or name of the index or heap (for dictionary encoded strings). |
| "type" | VARCHAR | sys.types.sqlname | The data type name. |
| "mode" | VARCHAR | | State of the column, such as writable. |
| "location" | VARCHAR | | The location (relative path and file name) of the persistent storage data file. |
| "count" | BIGINT | | The number of data values in the column storage. |
| "typewidth" | INTEGER | | Maximum width of the data in bytes for the column. Large objects without a maximum specified will have 0. |
| "columnsize" | BIGINT | | The total size in bytes of the column data. |
| "heapsize" | BIGINT | | The total size in bytes of the heap data of the column if existing such as when the column is CHAR(n) or VARCHAR(n) or CLOB(n) or BLOB(n). |
| "hashes" | BIGINT | | The total size in bytes of the hash data of a column if existing and loaded. |
| "phash" | BOOLEAN | | Whether a hash exists for this column? |
| "imprints" | BIGINT | | The total size in bytes of the imprints data of a column if existing. |
| "sorted" | BOOLEAN | | Whether the column data is sorted? |
| "revsorted" | BOOLEAN | | Whether the column data is reverse sorted? |
| "unique" | BOOLEAN | | Whether all values in the column data are distinct from each other? |
| "orderidx" | BIGINT | | The total size in bytes of the ordered index data of a column if existing. |
By changing the storagemodelinput table directly, the footprint for yet to be loaded databases can be assessed. See also Storage Model and Disk Space.
| name | type | references | description |
|---|
| "schema" | VARCHAR | sys.schemas.name | The schema name. |
| "table" | VARCHAR | sys.tables.name | The table name. |
| "column" | VARCHAR | sys.columns.name | The column name or name of the index or heap (for dictionary encoded strings). |
| "type" | VARCHAR | sys.types.sqlname | The data type name. |
| "typewidth" | INTEGER | | Maximum width of the data in bytes for the column. Large objects without a maximum specified will have 0. |
| "count" | BIGINT | | The estimated number of tuples. |
| "distinct" | BIGINT | | Indication of distinct number of strings. |
| "atomwidth" | INTEGER | | Average width of strings or clob. |
| "reference" | BOOLEAN | | Whether the column is used as foreign key reference? |
| "sorted" | BOOLEAN | | Whether the column data is sorted? If set there is no need for an index. |
| "unique" | BOOLEAN | | Whether all values in the column data are distinct from each other? |
| "isacolumn" | BOOLEAN | | Whether the storage is a column |
sys.storagemodel
| name | type | references | description |
|---|
| "schema" | VARCHAR | sys.schemas.name | The schema name. |
| "table" | VARCHAR | sys.tables.name | The table name. |
| "column" | VARCHAR | sys.columns.name | The column name or name of the index or heap (for dictionary encoded strings). |
| "type" | VARCHAR | sys.types.sqlname | The data type name. |
| "count" | BIGINT | | The number of data values in the column. |
| "columnsize" | BIGINT | | The total size in bytes of the column. |
| "heapsize" | BIGINT | | The total size in bytes of the heap of the column if existing such as when the column is CHAR(n) or VARCHAR(n) or CLOB(n) or BLOB(n). |
| "hashsize" | BIGINT | | The total size in bytes of the hash of a column data if existing. |
| "imprintsize" | BIGINT | | The total size in bytes of the imprint of a column data if existing. |
| "orderidxsize" | BIGINT | | The total size in bytes of the ordered indexx of a column data if existing. |
| "sorted" | BOOLEAN | | Whether the column data is sorted? |
| "unique" | BOOLEAN | | Whether all values in the column data are distinct from each other? |
| "isacolumn" | BOOLEAN | | Whether the storage is a column |
sys.tablestoragemodel
| name | type | references | description |
|---|
| "schema" | VARCHAR | sys.schemas.name | The schema name. |
| "table" | VARCHAR | sys.tables.name | The table name. |
| "rowcount" | BIGINT | | The number of rows in the table. |
| "storages" | BIGINT | | The number of storage files used by the table. Besides column storage files also indices and dictionary heaps and pkey hashes require a storage file. |
| "columnsize" | BIGINT | | The total size in bytes of all the column data of the table. |
| "heapsize" | BIGINT | | The total size in bytes of all the column heap data of the table. |
| "hashsize" | BIGINT | | The total size in bytes of all the column hash data of the table. |
| "imprintsize" | BIGINT | | The total size in bytes of all the column imprints data of the table. |
| "orderidxsize" | BIGINT | | The total size in bytes of all the ordered column indices data of the table. |