How much storage space to VARCHAR columns occupy?
Article ID: 16181
Updated On:
Products
Issue/Introduction
In an SQL-defined table, columns holding character data can be defined as either CHARACTER or VARCHAR.
How much storage do VARCHAR columns occupy? Do they take up only the amount of significant data in each individual occurrence of the column, or do they occupy the maximum amount as defined in the table?
Environment
IDMS SQL Option, all supported releases.
Resolution
VARCHAR columns occupy the maximum amount as defined in the table, regardless of the actual length of a particular column instance.
For example, consider this table definition and row data:-
CREATE TABLE CHARTEST
( CALCKEY UNSIGNED NUMERIC(4) NOT NULL,
VARCHARCOL VARCHAR(1024) NOT NULL,
CHARCOL CHARACTER(4) NOT NULL,
ENDCOL CHARACTER(6) NOT NULL
)
;
INSERT INTO CHARTEST VALUES (1,'VARCHAR','CHAR','ENDCOL');
Look at the row data as it appears in the output of a PRINT PAGE:
*+ 000230 404040AB F0F0F0F1 0007E5C1 D9C3C8C1 * .0001..VARCHA**+ 000240 D9000000 00000000 00000000 00000000 *R...............**+ 000250 00000000 00000000 00000000 00000000 *................**+ 000260 --SAME--*+ 000630 00000000 00000000 0000C3C8 C1D9C5D5 *..........CHAREN**+ 000640 C4C3D6D3 03D47E00 03D47E02 03D48002 *DCOL.M=..M=..M..*
Column VARCHARCOL is in red. It starts at offset x’238’ with the length halfword value of 7. Then the data starts and runs from offset x’23A’ to x’63A’ where the column CHAR starts. This is a length of x’400’, or 1024 as defined in the column definition.
Note also that the CHAR column, in green, only occupies 4 bytes and there is no length halfword. This is the only difference between CHAR and VARCHAR in terms of data storage.
IDMS Presspack can be used to compress SQL tables in general.
Additional Information
Feedback
