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

nametypereferencesdescription
"schema"VARCHARsys.schemas.nameThe schema name.
"storages"BIGINTThe total number of storage files used by all tables in the schema.
"columnsize"BIGINTThe total size in bytes of all the column data of all tables in the schema.
"heapsize"BIGINTThe total size in bytes of all the column heap data of all tables in the schema.
"hashsize"BIGINTThe total size in bytes of all the column hash data of all tables in the schema.
"imprintsize"BIGINTThe total size in bytes of all the column imprints data of all tables in the schema.
"orderidxsize"BIGINTThe total size in bytes of all the ordered column indices data of all tables in the schema.

sys.tablestorage

nametypereferencesdescription
"schema"VARCHARsys.schemas.nameThe schema name.
"table"VARCHARsys.tables.nameThe table name.
"rowcount"BIGINTThe number of rows in the table.
"storages"BIGINTThe 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"BIGINTThe total size in bytes of all the column data of the table.
"heapsize"BIGINTThe total size in bytes of all the column heap data of the table.
"hashsize"BIGINTThe total size in bytes of all the column hash data of the table.
"imprintsize"BIGINTThe total size in bytes of all the column imprints data of the table.
"orderidxsize"BIGINTThe total size in bytes of all the ordered column indices data of the table.

sys.storage

nametypereferencesdescription
"schema"VARCHARsys.schemas.nameThe schema name.
"table"VARCHARsys.tables.nameThe table name.
"column"VARCHARsys.columns.nameThe column name or name of the index or heap (for dictionary encoded strings).
"type"VARCHARsys.types.sqlnameThe data type name.
"mode"VARCHARState of the column, such as writable.
"location"VARCHARThe location (relative path and file name) of the persistent storage data file.
"count"BIGINTThe number of data values in the column storage.
"typewidth"INTEGERMaximum width of the data in bytes for the column. Large objects without a maximum specified will have 0.
"columnsize"BIGINTThe total size in bytes of the column data.
"heapsize"BIGINTThe 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"BIGINTThe total size in bytes of the hash data of a column data if existing.
"phash"BOOLEANWhether it has a primary key hash?
"imprints"BIGINTThe total size in bytes of the imprints data of a column data if existing.
"sorted"BOOLEANWhether the column data is sorted?
"revsorted"BOOLEANWhether the column data is reverse sorted?
"unique"BOOLEANWhether all values in the column data are distinct from each other?
"orderidx"BIGINTThe total size in bytes of the ordered index data of a column data if existing.

sys.storagemodelinput

By changing the storagemodelinput table directly, the footprint for yet to be loaded databases can be assessed. See also Storage Model and Disk Space.

nametypereferencesdescription
"schema"VARCHARsys.schemas.nameThe schema name.
"table"VARCHARsys.tables.nameThe table name.
"column"VARCHARsys.columns.nameThe column name or name of the index or heap (for dictionary encoded strings).
"type"VARCHARsys.types.sqlnameThe data type name.
"typewidth"INTEGERMaximum width of the data in bytes for the column. Large objects without a maximum specified will have 0.
"count"BIGINTThe estimated number of tuples.
"distinct"BIGINTIndication of distinct number of strings.
"atomwidth"INTEGERAverage width of strings or clob.
"reference"BOOLEANWhether the column is used as foreign key reference?
"sorted"BOOLEANWhether the column data is sorted? If set there is no need for an index.
"unique"BOOLEANWhether all values in the column data are distinct from each other?
"isacolumn"BOOLEANWhether the storage is a column

sys.storagemodel

nametypereferencesdescription
"schema"VARCHARsys.schemas.nameThe schema name.
"table"VARCHARsys.tables.nameThe table name.
"column"VARCHARsys.columns.nameThe column name or name of the index or heap (for dictionary encoded strings).
"type"VARCHARsys.types.sqlnameThe data type name.
"count"BIGINTThe number of data values in the column.
"columnsize"BIGINTThe total size in bytes of the column.
"heapsize"BIGINTThe 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"BIGINTThe total size in bytes of the hash of a column data if existing.
"imprintsize"BIGINTThe total size in bytes of the imprint of a column data if existing.
"orderidxsize"BIGINTThe total size in bytes of the ordered indexx of a column data if existing.
"sorted"BOOLEANWhether the column data is sorted?
"unique"BOOLEANWhether all values in the column data are distinct from each other?
"isacolumn"BOOLEANWhether the storage is a column

sys.tablestoragemodel

nametypereferencesdescription
"schema"VARCHARsys.schemas.nameThe schema name.
"table"VARCHARsys.tables.nameThe table name.
"rowcount"BIGINTThe number of rows in the table.
"storages"BIGINTThe 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"BIGINTThe total size in bytes of all the column data of the table.
"heapsize"BIGINTThe total size in bytes of all the column heap data of the table.
"hashsize"BIGINTThe total size in bytes of all the column hash data of the table.
"imprintsize"BIGINTThe total size in bytes of all the column imprints data of the table.
"orderidxsize"BIGINTThe total size in bytes of all the ordered column indices data of the table.