Varchar vs. text

[Berislav Lopac]

I was lately wandering what would be the advantage of using varchar instead
of text column data type in a database (specificall MySQL, but this might
also be the case for other databases)? I mean, both of them are variable
length, both can be indexed, and text can hold a lot more data...

I'm just looking for some other opinions on the subjects.

Berislav

[Jerome H. Gitomer]

Re: Varchar vs. text

Berislav Lopac wrote:[color=blue]
> I was lately wandering what would be the advantage of using varchar instead
> of text column data type in a database (specificall MySQL, but this might
> also be the case for other databases)? I mean, both of them are variable
> length, both can be indexed, and text can hold a lot more data...
>
> I'm just looking for some other opinions on the subjects.
>
> Berislav
>
>[/color]
I prefer VARCHAR since some other RDBMS support VARCHAR but not
TEXT. If you will never migrate it doesn't matter, but if you
ever do it may.

HTH
Jerry

[Tim Van Wassenhove]

Re: Varchar vs. text

On 2004-01-26, Berislav Lopac <berislav.lopac @dimedia.hr> wrote:[color=blue]
> I was lately wandering what would be the advantage of using varchar instead
> of text column data type in a database (specificall MySQL, but this might
> also be the case for other databases)? I mean, both of them are variable
> length, both can be indexed, and text can hold a lot more data...
>
> I'm just looking for some other opinions on the subjects.[/color]

The exact differences are mentionned in the MySQL manual.

--

http://home.mysth.be/~timvw

[Jochen Daum]

Re: Varchar vs. text

hi!

On Mon, 26 Jan 2004 15:41:06 +0100, "Berislav Lopac"
<berislav.lopac @dimedia.hr> wrote:
[color=blue]
>I was lately wandering what would be the advantage of using[/color]
varchar instead[color=blue]
>of text column data type in a database (specificall MySQL, but this might
>also be the case for other databases)? I mean, both of them are variable
>length, both can be indexed, and text can hold a lot more data...
>
>I'm just looking for some other opinions on the subjects.[/color]

Some DBMS store a text in a separate page (eg. MSSQL 7 and 2000 [by
default]), so you would have an unnecessary page hit for certain size
varchars.

HTH, Jochen
--
Jochen Daum - CANS Ltd.
PHP DB Edit Toolkit -- PHP scripts for building
database editing interfaces.

http://sourceforge.net/projects/phpdbedittk/

[Capt. Beefheart]

Re: Varchar vs. text

Berislav

I would imagine that varchar and text are from the days when disk space
was at a premium, that is, there wasn't much of it. Consquently varchar
would be used unless large amounts of text were going to be inserted
into a field. Of course with the problem of disk space no longer an
issue varchar could be considered defunct. Well at least in MySQL. Other
RDBMS's only use BLOB fields which can't be indexed.

Beefy

Berislav Lopac wrote:[color=blue]
> I was lately wandering what would be the advantage of using varchar instead
> of text column data type in a database (specificall MySQL, but this might
> also be the case for other databases)? I mean, both of them are variable
> length, both can be indexed, and text can hold a lot more data...
>
> I'm just looking for some other opinions on the subjects.
>
> Berislav
>
>[/color]

[Jochen Daum]

Re: Varchar vs. text

Hi Berislav!

On Mon, 26 Jan 2004 21:22:56 +0000, "Capt. Beefheart"
<Captain.Beefhe art@crystalfall s.com> wrote:
[color=blue]
>Berislav
>
>I would imagine that varchar and text are from the days when disk space
>was at a premium, that is, there wasn't much of it. Consquently varchar
>would be used unless large amounts of text were going to be inserted
>into a field. Of course with the problem of disk space no longer an
>issue varchar could be considered defunct. Well at least in MySQL. Other
>RDBMS's only use BLOB fields which can't be indexed.[/color]

Not diskspace directky, but disk page accesses are *THE* premium, when
working with databases. Nearly all optimisations come down to
minimizing disk access. As soon as your database grows out of your
main memory, it is an issue.

HTH, Jochen[color=blue]
>
>Beefy
>
>Berislav Lopac wrote:[color=green]
>> I was lately wandering what would be the advantage of using varchar instead
>> of text column data type in a database (specificall MySQL, but this might
>> also be the case for other databases)? I mean, both of them are variable
>> length, both can be indexed, and text can hold a lot more data...
>>
>> I'm just looking for some other opinions on the subjects.
>>
>> Berislav
>>
>>[/color][/color]

--
Jochen Daum - CANS Ltd.
PHP DB Edit Toolkit -- PHP scripts for building
database editing interfaces.

http://sourceforge.net/projects/phpdbedittk/

[Chung Leong]

Re: Varchar vs. text

Yup. Text/ntext fields are agonizingly slow in MSSQL 2000. Rows are limited
to 8000 bytes, so sometimes you're forced to use them. Accessing varchar
wider than 255 is a major pain too using PHP.

Uzytkownik "Jochen Daum" <jochen.daum@ca ns.co.nz> napisal w wiadomosci
news:vjta10h9rh a6aj16aolcuijlv ph5i6r8dr@4ax.c om...[color=blue]
> hi!
>
> On Mon, 26 Jan 2004 15:41:06 +0100, "Berislav Lopac"
> <berislav.lopac @dimedia.hr> wrote:
>[color=green]
> >I was lately wandering what would be the advantage of using[/color]
> varchar instead[color=green]
> >of text column data type in a database (specificall MySQL, but this might
> >also be the case for other databases)? I mean, both of them are variable
> >length, both can be indexed, and text can hold a lot more data...
> >
> >I'm just looking for some other opinions on the subjects.[/color]
>
> Some DBMS store a text in a separate page (eg. MSSQL 7 and 2000 [by
> default]), so you would have an unnecessary page hit for certain size
> varchars.
>
> HTH, Jochen
> --
> Jochen Daum - CANS Ltd.
> PHP DB Edit Toolkit -- PHP scripts for building
> database editing interfaces.
> http://sourceforge.net/projects/phpdbedittk/[/color]

[Jochen Daum]

Re: Varchar vs. text


Hi Chung!

On Mon, 26 Jan 2004 21:29:12 -0500, "Chung Leong"
<chernyshevsky@ hotmail.com> wrote:
[color=blue]
>Yup. Text/ntext fields are agonizingly slow in MSSQL 2000. Rows are limited
>to 8000 bytes, so sometimes you're forced to use them. Accessing varchar
>wider than 255 is a major pain too using PHP.[/color]

I actually thought this is a FreeTDS issue. If you set the Version to
7.0, eg. export TDSVER=7.0, (or similar for SQL 2000) you have no
problem retrieving 8000 chars.

For most applications I'm quite happy to use varchar. If it gets
bigger you have to consider the backup process as well anyway and then
it gets tricky.

HTH, Jochen
[color=blue]
>
>Uzytkownik "Jochen Daum" <jochen.daum@ca ns.co.nz> napisal w wiadomosci
>news:vjta10h9r ha6aj16aolcuijl vph5i6r8dr@4ax. com...[color=green]
>> hi!
>>
>> On Mon, 26 Jan 2004 15:41:06 +0100, "Berislav Lopac"
>> <berislav.lopac @dimedia.hr> wrote:
>>[color=darkred]
>> >I was lately wandering what would be the advantage of using[/color]
>> varchar instead[color=darkred]
>> >of text column data type in a database (specificall MySQL, but this might
>> >also be the case for other databases)? I mean, both of them are variable
>> >length, both can be indexed, and text can hold a lot more data...
>> >
>> >I'm just looking for some other opinions on the subjects.[/color]
>>
>> Some DBMS store a text in a separate page (eg. MSSQL 7 and 2000 [by
>> default]), so you would have an unnecessary page hit for certain size
>> varchars.
>>
>> HTH, Jochen
>> --
>> Jochen Daum - CANS Ltd.
>> PHP DB Edit Toolkit -- PHP scripts for building
>> database editing interfaces.
>> http://sourceforge.net/projects/phpdbedittk/[/color]
>[/color]

--
Jochen Daum - CANS Ltd.
PHP DB Edit Toolkit -- PHP scripts for building
database editing interfaces.

http://sourceforge.net/projects/phpdbedittk/