Skip to Content
0

Does SAP IQ adding blanks in the CHAR datatype?

Dec 12, 2016 at 02:32 PM

93

avatar image

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

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

2 Answers

Tayeb Hadjou
Dec 14, 2016 at 08:41 AM
0

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,

Share
10 |10000 characters needed characters left characters exceeded
Robert Kratschmann Dec 21, 2016 at 01:16 PM
0

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

Share
10 |10000 characters needed characters left characters exceeded