By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,756 Members | 1,749 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,756 IT Pros & Developers. It's quick & easy.

varchar vs char

P: n/a
what's the difference of the two data types?
Nov 12 '05 #1
Share this Question
Share on Google+
10 Replies


P: n/a
Techie wrote:
what's the difference of the two data types?

var
Hope it helps,

Jan M. Nelken
Nov 12 '05 #2

P: n/a
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.

Nov 12 '05 #3

P: n/a
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
Nov 12 '05 #4

P: n/a
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.
Nov 12 '05 #5

P: n/a
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
Nov 12 '05 #6

P: n/a
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
Nov 12 '05 #7

P: n/a
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.
Nov 12 '05 #8

P: n/a
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
Nov 12 '05 #9

P: n/a
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! =-----
Nov 12 '05 #10

P: n/a
As Serge and Knut have said, VARCHAR only uses the space as defined in
the length when the data is inserted. Therefore the row length can be
different depending on the amount of data in the VARCHAR columns.

Each VARCHAR has a 2-byte overhead to store the length (a smallint is
used to store the length). Works exactly the same way in DB2 for z/OS
as it does for DB2 for LUW.
Nov 12 '05 #11

This discussion thread is closed

Replies have been disabled for this discussion.