Skip to Content

Does SAP IQ adding blanks in the CHAR datatype?

Hi,

the IQ Administration Training says:

CHAR() data types are faster for queries than VARCHAR(). CHAR() are blank padded. Use VARCHAR() for columns that are concatenated in queries because the CHAR() types will be padded. There is no storage advantage for choosing VARCHAR over CHAR.

While the documentation says (http://help.sap.com/saphelp_iq1611_iqrefbb/helpdata/en/a5/11839b84f210159df2d5d78c6e891f/content.htm)

VARCHAR: Same as CHAR, except that no blank padding is added to the storage of these strings, and VARCHAR strings can have a maximum length of (32KB – 1).

Question: Does the CHAR datatype in the IQ Store fill a short string with blank characters up to length of the datatype definition?

In the IQ Store a column definied as CHAR(10) will be store as "AB " (with 8 blanks) or as "AB"?

Many thanks

Robert

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Dec 14, 2016 at 08:41 AM

    Hi,

    Yes it does.

    create table test_tab (a char(10), b varchar(10));

    insert test_tab values('12', '12');

    select char_length(a), char_length(b) from test_tab

    10, 2

    select a || '34', b||'34' from test_tab

    12 34,1234

    Regards,

    Add comment
    10|10000 characters needed characters exceeded

  • Dec 21, 2016 at 01:16 PM

    Hi,

    from my point of view it's possible that the trailing spaces will be added only during the output? The following sample for t1 with a char column and t2 with a varchar column:

    drop table if exists t1;
    create table t1 (c1 char(255) iq unique (51000)); --table with char column
    begin
    declare @i integer;
    set @i=1;
    while @i<=50000 loop
    insert into t1 values (string(@i));
    set @i=@i+1;
    end loop;
    end;

    drop table if exists t2;
    create table t2 (c1 varchar(255) iq unique (51000)); --table with varchar column
    begin
    declare @i integer;
    set @i=1;
    while @i<=50000 loop
    insert into t2 values (string(@i));
    set @i=@i+1;
    end loop;
    end;


    sp_iqdbspaceinfo iq_main,dba,t1; and sp_iqdbspaceinfo iq_main,dba,t2; gave:

    dbspace_name object_type owner object_name object_id id columns indexes metadata primary_key unique_constraint foreign_key dbspace_online
    ------------ ----------- ----- ----------- ---------- ---------- ------- ------- -------- ----------- ----------------- ----------- --------------
    iq_main table DBA t1 44709 1273 160K 0B 2.98M 0B 0B 0B Y
    dbspace_name object_type owner object_name object_id id columns indexes metadata primary_key unique_constraint foreign_key dbspace_online
    ------------ ----------- ----- ----------- ---------- ---------- ------- ------- -------- ----------- ----------------- ----------- --------------
    iq_main table DBA t2 44712 1274 160K 0B 872K 0B 0B 0B Y

    sp_iqrowdensity 'table t1'; and sp_iqrowdensity 'table t2'; show that we have identical structures for both tables:
    Tablename Column Name IndexType Density
    --------- ----------- ------------------------ ----------------
    DBA.t2 c1 16 Bit FP 1.0

    The sp_iqdbspaceinfo output shows for t1 and t2: columns (Size of column storage space on the given dbspace) = 160K.
    Metadata shows for t1 2.98M and 872K for t2 - I have no idea why the metadata should be different while the column storage space is equal.

    Thanks and regards

    Robert

    Add comment
    10|10000 characters needed characters exceeded