CHAR vs VARCHAR

[Troels Arvin]

Hello,

In the DBMS I know best, PostgreSQL, there is no real performance
difference between CHAR and VARCHAR. And since CHAR pads with spaces, I
generally dislike CHAR (with CHAR, it's not possible to see if the input
value had trailing spaces or not, for example).

How is it in DB2?

More specifically: If a column has type CHAR(20) and most of the strings
have lengths between 5 and 15 characters, does it then make sense to use
CHAR(20)?

Are there any cases where CHAR are to be preferred over VARCHAR?

--
Regards,
Troels Arvin <tr****@arvin.d k>
http://troels.arvin.dk/

[James]

"Troels Arvin" <tr****@arvin.d kwrote in message
news:eq******** **@news.net.uni-c.dk...

Hello,

In the DBMS I know best, PostgreSQL, there is no real performance
difference between CHAR and VARCHAR. And since CHAR pads with spaces, I
generally dislike CHAR (with CHAR, it's not possible to see if the input
value had trailing spaces or not, for example).

How is it in DB2?

More specifically: If a column has type CHAR(20) and most of the strings
have lengths between 5 and 15 characters, does it then make sense to use
CHAR(20)?

Are there any cases where CHAR are to be preferred over VARCHAR?

--
Regards,
Troels Arvin

A VARCHAR requires 2 extra bytes to store the length. So if the column width
is very small, or it is always filled to capacity, then it saves space to
use CHAR. There is also a very small amount of extra processing for VARCHAR,
but it is usually not measurable.

One other consideration is whether a VARCHAR column gets updated after it is
inserted. If the column is updated and it is now larger, then the row may
not fit back where it was, and it may not fit back on the same page. This
could result in overflow pages. This can be alleviated by using the proper
percent free on the table, and by doing frequent reorgs as needed to make
sure there is enough free space and to reorg any overflow rows.