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 gives 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 view sys.storage (or for specific table via the table producing function storage(schema_name, table_name)). It represents the actual state of affairs, i.e. storage on disk of columns and foreign key indices, and possible temporary hash indices. For strings we take a sample to determine their average length.
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.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.
"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 column data file.
"count" BIGINT   The number of data values in the column.
"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.
"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).
"hashes" BIGINT   The total size in bytes of the hash of a column data if existing.
"phash" BOOLEAN   Whether it has a primary key hash?
"imprints" BIGINT   The total size in bytes of the imprint 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 of a column data if existing.

 

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.
"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).
"hashes" BIGINT   The total size in bytes of the hash of a column data if existing.
"imprints" BIGINT   The total size in bytes of the imprint 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 of a column data if existing.

 

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.
"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.
"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 of a column.

 

sys.tablestoragemodel
name type references description
"schema" VARCHAR sys.schemas.name The schema name.
"table" VARCHAR sys.tables.name The table name.
"count" BIGINT   The number of data rows in the table.
"columnsize" BIGINT   The total size in bytes of all columns in this table.
"heapsize" BIGINT   The total size in bytes of the heaps of all columns in this table.
"hashes" BIGINT   The total size in bytes of the hashes of all columns in this table.
"imprints" BIGINT   The total size in bytes of the imprints of all columns in this table.
"auxiliary" BIGINT   The maximum size in bytes if all non-sorted columns would be augmented with a hash (rare situation).