Need a bit of SQL help

Anybody here clued up on SQL? I'm totally new to it all so I apologise if this is a stupid question.

Suppose I have a field called 'Fullname' defined as Varchar(20)... but some names might be longer than that occasionally.

Is there a way to define a text field without it having a pre-defined maximum length?

There's not in SQL 2000, So just set it to VarChar(100), varchars won't store whitespace so it's not a biggy, although it's best to keep it to a minimum for indexing.

If you're int SQL 2005 then there's VarChar(max)

Char fields store whitespace, no idea what the point of them is, I'm guessing they might be slightly faster for some things, but never really looked into it.

VarChar(255) works well too.

I doubt the difference is noticeable.

You could use a text field, but it's really not worth it at all.

Just enlarging the varchar field will suffice :)

Flame,

Just found this article, it does a comparison between the performance of varchar and char fields... And pretty much concludes there's bugger all difference! Still, quite interesting!
http://sqljunkies.com/WebLog/odds_and_ends/archive/2005/09/12/16725.aspx

And speaking of performance there's not gain from using ^2 values in field sizes, but I still do it all the time!

Sure, but in my experience not every database supports VARCHAR columns with more than 255 elements (MySQL 4 for sure, I'm certain there are others tho').

just set it as a text field. not sure of the limit on it but its high.

But I would suggest just upping the character length instead as it saves space, 30 or 40 should be more than enough.

Possibly, I use Transact SQL 2000 and 2005 varchar supports upto 8000, varchar(max) (in 2005) is pretty much the same as varchar(8000).

That said using varchar(max) means when there's a new release of SQL and the varchar field is extended the varchar(max) fields will extend too when you migrate so I guess it makes it more future proof.

I had no idea MySQL only allowed for 255... That's crap! But I suppose it's free! That said MySQL is just wierd and has 'interesting' stored procedure implementation.

The Text field allows 2,147,483,647 characters in SQL 2005 which may be described as overkill for a person's name?! ;)

Yes quite. In either case this is a none-issue as of MySQL version 5 - however lots of places are still running on MySQL4 (and even 3).

Also I'm fairly sure a TEXT field is just a special case of a BLOB - which does come with a performance hit compared to VARCHAR.

the text field should be no longer than the largest string stored in that column - ie. it's dynamic.

I've done a bit of reading up on the Varchar limitation in MySQL 4 (since I'm using MySQL 4.1.21).

MySQL 4.0 - 255 bytes
MySQL 4.1 - 64Kb