varchar vs char

[Techie]

what's the difference of the two data types?

[Jan M. Nelken]

Techie wrote:

what's the difference of the two data types?

var
Hope it helps,

Jan M. Nelken

[Robert Stearns]

Techie wrote:

what's the difference of the two data types?

The major behavioral difference I have found is the effect of trailing
blanks. With varchar, there are none unless you place them in the data
item. With char, the data is always padded out to the length of of the
data item. This can bite you in many different contexts and requires
liberal application of rtrim. IMHO char(n) should only when you certain
that now and in the future the data will always be n characters long. In
every other case varchat(n) should be used.

[db2inst2]

The char is a fixed-length character data type, the varchar is a
variable-length character data type.

Because char is a fixed-length data type, the storage size of the char
value is equal to the maximum size for this column. Because varchar is
a variable-length data type, the storage size of the varchar value is
the actual length of the data entered, not the maximum size for this
column.

You can use char when the data entries in a column are expected to be
the same size.
You can use varchar when the data entries in a column are expected to
vary considerably in size.

http://www.mssqlcity.com/FAQ/General...vs_varchar.htm

[DB2SQL]

From some answers here somebody could get a wrong idea that VARCHAR
field will take different size in different rows, so VARCHAR could
save storage space.

The VARCHAR field will be same size in every row for particular
column. VARCHAR will take always two bytes more than CHAR for actual
storage, because it contains the actual length of data information
(16-bit integer).

Difference between CHAR and VARCHAR is in the speed. For same defined
length, VARCHAR could be faster if data in the CHAR column have to be
padded with space. In the VARCHAR type column, data beyond actual
length exist but are ignored.

For same defined length VARCHAR will be for two bytes bigger then
CHAR.

If you want to save space define the CHAR type column. If you need
faster processing define VARCHAR type column.

In DB2, unless you have BLOB type columns, all records are same size.

[Serge Rielau]

DB2SQL,

That is not correct for DB2 UDB for LUW (I can't comment on DB2 z/OS and
DB2 AS/400 internals).
DB2 for LUW will, for each row, only allocate the two bytes length, a
null indicator byte if required, and the space for the data.

Cheers
Serge

[Jan M. Nelken]

Serge Rielau wrote:

DB2SQL,

That is not correct for DB2 UDB for LUW (I can't comment on DB2 z/OS and
DB2 AS/400 internals).
DB2 for LUW will, for each row, only allocate the two bytes length, a
null indicator byte if required, and the space for the data.

Cheers
Serge

Actually, default length of the length field in VARCHAR is 4 bytes.

Jan

[DB2SQL]

You first said it is not, and after that you said DB2 allocates the
space for data.

Of course, I am talking about allocated space of data. For a VARCHAR
field, the allocated space of data is same for each row regardless of
actual content of the data.

The VARCHAR field is not a BLOB. The VARCHAR field is allocated and
stored fully inside the row, so each row has same size.

There is several ways to prove that. For example, create a database
and a table with one VARCHAR field and insert million rows with an
empty VARCHAR field. Drop the database and repeat it again with some
content in the VARCHAR field. Or just take a closer look into SQLDA
with debugger.

[Knut Stolze]

DB2SQL wrote:

You first said it is not, and after that you said DB2 allocates the
space for data.

Of course, I am talking about allocated space of data. For a VARCHAR
field, the allocated space of data is same for each row regardless of
actual content of the data.
No, not for VARCHAR data. The string 'some text' requires 12 bytes on disc
(2 length + 1 null-indicator + 9 data bytes) whereas 'some more text' uses
17 bytes on disc (2 + 1 + 14 data bytes).
The VARCHAR field is not a BLOB. The VARCHAR field is allocated and
stored fully inside the row,
true
so each row has same size.
not true

The behavior you describe is valid for CHARACTER (CHAR) columns, though.
There is several ways to prove that. For example, create a database
and a table with one VARCHAR field and insert million rows with an
empty VARCHAR field. Drop the database and repeat it again with some
content in the VARCHAR field. Or just take a closer look into SQLDA
with debugger.

Well, only 255 rows fit on a single 4K data page. If your "some content"
shorter than some 15 bytes, you will not use the complete page for 255 rows
at all. Then, the same number of rows and 10 bytes VARCHAR data needs the
same amount of pages as a 4 bytes VARCHAR data. You should rather compare
rows with '' (empty string) and rows with REPEAT('x', 3900) and then have a
look at the tablespace details (LIST TABLESPACES SHOW DETAIL) to see how
many pages were allocated in each case. You will see a huge difference
there.

--
Knut Stolze
Information Integration
IBM Germany / University of Jena

[Ian]

DB2SQL wrote:

Of course, I am talking about allocated space of data. For a VARCHAR
field, the allocated space of data is same for each row regardless of
actual content of the data.

The VARCHAR field is not a BLOB. The VARCHAR field is allocated and
stored fully inside the row, so each row has same size.
The data is stored within the page, correct. But DB2 does not reserve
the full length of the varchar. This is why you can have rows that
overflow (i.e. an update to a VARCHAR could not fit on to the page,
so the whole row is moved to another page and a pointer is left on
the original page).

There is several ways to prove that. For example, create a database
and a table with one VARCHAR field and insert million rows with an
empty VARCHAR field. Drop the database and repeat it again with some
content in the VARCHAR field. Or just take a closer look into SQLDA
with debugger.

If you create a table with 1 column VARCHAR(254), and insert 10,000
records with a 200-character long string, vs. 10000 records with a
10-character string, you'll see roughly 20x more utilization.

-----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
-----== Over 100,000 Newsgroups - 19 Different Servers! =-----