Skip to main content

Storage of tables and columns

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 data if existing.
"phash" BOOLEAN   Whether it has a primary key hash?
"imprints" BIGINT   The total size in bytes of the imprints data of a column data 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 data 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

sys.storagemodelinput
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.