char(1) vs varchar(1)
sreeblr
- Select Field - Employee
This regarding usage of data type Varchar and char for indicator columns .
I did a sample test to check the bytes used for each data type and there is no difference between bytes used for char or varchar.
Which data type occupies less space and is preferred for the usage where length is 1.
create table test_char_sri (col1 char(1));
create table test_vchar_sri (col1 varchar(1));
SELECT anchor_table_name,sum(used_bytes)
FROM
v_monitor.projection_storage
where anchor_table_name in ('test_vchar_sri','test_char_sri')
group by anchor_table_name
test_char_sri 312
test_vchar_sri 312
select * from ua3_pium.test_char_sri;
(null)
a
b
c
d
f
g
h
select * from ua3_pium.test_vchar_sri;
(null)
a
b
c
d
f
g
h
I did a sample test to check the bytes used for each data type and there is no difference between bytes used for char or varchar.
Which data type occupies less space and is preferred for the usage where length is 1.
create table test_char_sri (col1 char(1));
create table test_vchar_sri (col1 varchar(1));
SELECT anchor_table_name,sum(used_bytes)
FROM
v_monitor.projection_storage
where anchor_table_name in ('test_vchar_sri','test_char_sri')
group by anchor_table_name
test_char_sri 312
test_vchar_sri 312
select * from ua3_pium.test_char_sri;
(null)
a
b
c
d
f
g
h
select * from ua3_pium.test_vchar_sri;
(null)
a
b
c
d
f
g
h
0
Comments
use char if the length is going to be 1.
For fields with fixed length char is a good candidate.
What are advantages and disadvantages in terms of having char and varchar .
For oracle it helps to have char for fixed length since we can calculate the space /usage and design tablespace accordingly. However as i could get for used bytes in queries in my post using char /varchar doesnt matter. Also any difference in terms of performance.