473,396 Members | 1,599 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,396 software developers and data experts.

Varchar vs. text

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
Jul 17 '05 #1
7 19677
Berislav Lopac wrote:
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

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

Jul 17 '05 #2
On 2004-01-26, Berislav Lopac <be************@dimedia.hr> wrote:
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.


The exact differences are mentionned in the MySQL manual.

--
http://home.mysth.be/~timvw
Jul 17 '05 #3
hi!

On Mon, 26 Jan 2004 15:41:06 +0100, "Berislav Lopac"
<be************@dimedia.hr> wrote:
I was lately wandering what would be the advantage of using varchar insteadof 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.


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/
Jul 17 '05 #4
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:
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


Jul 17 '05 #5
Hi Berislav!

On Mon, 26 Jan 2004 21:22:56 +0000, "Capt. Beefheart"
<Ca***************@crystalfalls.com> wrote:
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.
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
Beefy

Berislav Lopac wrote:
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


--
Jochen Daum - CANS Ltd.
PHP DB Edit Toolkit -- PHP scripts for building
database editing interfaces.
http://sourceforge.net/projects/phpdbedittk/
Jul 17 '05 #6
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" <jo*********@cans.co.nz> napisal w wiadomosci
news:vj********************************@4ax.com...
hi!

On Mon, 26 Jan 2004 15:41:06 +0100, "Berislav Lopac"
<be************@dimedia.hr> wrote:
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.


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/

Jul 17 '05 #7

Hi Chung!

On Mon, 26 Jan 2004 21:29:12 -0500, "Chung Leong"
<ch***********@hotmail.com> wrote:
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.
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

Uzytkownik "Jochen Daum" <jo*********@cans.co.nz> napisal w wiadomosci
news:vj********************************@4ax.com.. .
hi!

On Mon, 26 Jan 2004 15:41:06 +0100, "Berislav Lopac"
<be************@dimedia.hr> wrote:
>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.


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/


--
Jochen Daum - CANS Ltd.
PHP DB Edit Toolkit -- PHP scripts for building
database editing interfaces.
http://sourceforge.net/projects/phpdbedittk/
Jul 17 '05 #8

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
by: adrian | last post by:
hi all this is my first post to this group, so pls bear with me while i try to make some sense. i am trying to create a sproc which uses dynamic sql to target a particuar table eg. '.' and...
7
by: Rick Caborn | last post by:
Does anyone know of a way to execute sql code from a dynamically built text field? Before beginning, let me state that I know this db architecture is built solely for frustration and I hope to...
2
by: Karen Sullivan | last post by:
Hi, all. I'm fairly new to SQL, and I have been trying to create a table from a text file. I have been looking at this for days, and can't find the problem. I get a syntax error " Line 55:...
3
by: m.ramana | last post by:
Given a string it should convert it to a proper text. Example: if you passed a string 'Cat in the hat', I want 'Cat In The Hat' Curious about few things, Does sql have Instr OR Split(like VB)...
12
by: Frederik Vanderhaeghe | last post by:
Hi, I have a problem converting text to a double. Why doesn't the code work: If Not (txtdocbedrag.Text = "") Then Select Case ddlBedrag.SelectedIndex Case 0 Case 1
11
by: panic attack | last post by:
Hello everbody, Our system is using Sql Server 2000 on Windows XP / Windows 2000 We have a text file needs to be imported into Sql Server 2000 as a table. But we are facing a problem which is,...
3
by: Daniel Wetzler | last post by:
Dear MSSQL- experts, I have a strange problem with SQLSERVER 2000. I tried to export a table of about 40000 lines into a text file using the Enterprise manager export assitant. I was astonished...
0
Krishna Ladwa
by: Krishna Ladwa | last post by:
In Sql Server 2000 Version, I found that no Notification message box appears when converting text column to varchar but the data gets truncated to the given size for the varchar. Whereas it appears...
4
by: Nick Chan | last post by:
all these while i've only used varchar for any string i heard from my ex-boss that char helps speed up searches. is that true? so there are these: 1) char with index 2) char without index...
1
by: codferrow | last post by:
Hello, I have a small issue with a web-search engine I'm working on. The main table is constantly growing (1 insert per second, currently 150 000 records) and it has full-text indexes on 2 fields...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
0
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
0
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
0
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
0
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.