Differences among VARCHAR, VARCHAR2, and CHAR

[jrefa...@hotmail.com]

What are the major differences among data type VARCHAR, VARCHAR2, and
CHAR?

please advise. thanks!!

[Uri Dimant]

Hi
What is VARCHAR2 ?

Using VARCHAR(n) , SQL Server will store one byte per chracter so declared
but unused chracters don't consume a storage

As opposed using CHAR(n) it will store one byte per character declared even
partially unused

<jrefa...@hotmail.com> wrote in message
news:1121403112.1...@g44g2000cwa.googlegroups.com...

[Jens Süßmeyer]

Varchar2 is used in Oracle not in SQL Server (I ever wondered why it is
namend with the suffix 2 ?)

Jens

[JT]

It is also worth noting that each VarChar column has the additional overhead
of a 2 byte header which stored bits related to the length of data. So
VarChar(2) actually consumes 4 bytes of storage when full and 2 bytes when
NULL. However, Char(2) consumes 2 bytes when full and 0 bytes when NULL.
Therefore, Char is more storage efficient when storing fixed length strings
like state codes and phone numbers, while VarChar is more efficient for
variable lenth strings of fairly long length, such as address. Even a
variable length string of 2 - 4 bytes might be better off as a Char to avoid
the overhead of a VarChar.

"Uri Dimant" <ur...@iscar.co.il> wrote in message
news:enngKrPi...@TK2MSFTNGP09.phx.gbl...

[Gert-Jan Strik]

JT, this is (partially) incorrect.

A char(2) column in SQL-Server 7.0 and later will always occupy 2 bytes
plus 1 bit overhead (for the NULL-bitmap entry). So a char(80) column
will occupy 80 bytes plus 1 bit overhead, regardless whether it is NULL
or not.

A varchar(2) column will occupy the actual data size (0 - 2 bytes) plus
2 bytes and 1 bit overhead (plus each row will use one extra byte simply
because you are using one or more varying length data types). So this
result in a minimum of 2 + 1 bit and a maximum of 4 bytes + 1 bit. A
varchar(80) will therefore occupy between 2 and 82 bytes + 1 bit.

So if you don't use trailing blanks, and you don't mind trimming
trailing blanks, then from a space efficiency point of view one should
not use Varchar for columns of 3 bytes or less and not use Char for
columns with average data size less than the defined size - 3.

Gert-Jan

[JT]

Thanks for typing up the loose ends on that one.

"Gert-Jan Strik" <so...@toomuchspamalready.nl> wrote in message
news:42D8173B...@toomuchspamalready.nl...