SQL Variable sizes

[jverkoey]

I've been wondering what exactly is the benefit of using a TEXT instead of a VARCHAR(10000) variable or something like that?

If I want to have a limit of 10000 letters, it's best to use a varchar right? Or is it best to use a text as maybe they take up less space?



--edit--

hmm, when I set a varchar(15000) in sql it just automatically changes it to TEXT...I guess that answers my question *shrugs*

[Thantos]

Well let me phrase your question like this:

What is better to use:

char foo[10000];

or

std::string foo;

Edit: I use varchar when the size is less then 100 or when I know it will be a certain size. If you want to impose a limit you do it through the software prior to inserting it into the table.

[jverkoey]

I was mainly wondering what the overhead of using text is. Because if there isn't any overhead....why not use it all the time? But I don't want to go and use something that either imposes a bad practice or is just not a good idea.

And yah, I put the software limit in there when the form gets submitted to keep it from extending 10000 characters.

[Perspective]

>>I've been wondering what exactly is the benefit of using a TEXT instead of a VARCHAR

The benifit of using TEXT is that it takes advantage of one of MySQL's variable length fields. The disadvantage is that TEXT (IIRC) is NOT an ANSI SQL data type, it is specific to MySQL. If you decided to swap to a PostgreSQL or Oracle backend for example, your app might not work.

[gsoft]

Another benefit would be with TEXT you have the FULLTEXT search capabilities.