Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

char(1) vs varchar(1)

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
 
 

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.

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.