Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

Is varchar just a remnant from before text came around, or are there use cases where you would want to use a varchar? (Or char for that matter..)

(I use Postgres and MySQL (MyISAM) daily, so those are what I'm most interested in, but answers for other databases are of course welcome. ^_-)

share|improve this question
4  
At least for SQL Server, text is deprecated. There are also considerations of usage that are related to where the data is stored and how it therefore gets accessed. –  Oded Jul 9 '12 at 19:08
    
On some DBMSs you may not be able to use a text column in a sort or where clause. I'm not familiar with Postgres but check your documentation. –  james Jul 10 '12 at 0:12

6 Answers 6

up vote 6 down vote accepted

In General

text columns are non standard and implementation specific. In many cases, depending on the database they may have a combination of one or more of the following restrictions: not indexable, not searchable and not sortable.

In Postgres

All the types map to the same implementation details under the hood.

In MySQL

The text column is a specialized version of BLOB and has restrictions on indexing.

share|improve this answer
    
Accepted for non standard and implementation specific and not indexable, not searchable and not sortable, which I didn't realize. I was under the impression text was standardized. –  Izkata Aug 8 '12 at 2:48
    
do you mean the ASCII text standard or the UNICODE text standard :-) or one of the other half dozen text encoding standards? –  Jarrod Roberson Aug 8 '12 at 3:19
    
Nono, I really mean as a database datatype - one that was, perhaps, added after char and varchar =P –  Izkata Aug 8 '12 at 3:26
1  
if you go digging through the SQL standards documents I don't think you will find anything about text as a character type. I haven't seen anything, some vendors call it long char and the like, it is basically a BLOB with an encoding attached to it. –  Jarrod Roberson Aug 8 '12 at 15:21

text, varchar and char are all used for different reasons. There are of course implementation differences (how much size they occupy .. etc), but also there are usage and intent considerations. What type you use also tells you something about the kind of data that will be stored in it (or we'd all use text for everything). If something has a fixed length, we use char. If it has variable length with a well defined upper limit then use varchar. If it's a big chunk of text that you have little control over then text would be probably your best bet.

share|improve this answer
    
I asked a similar question a little while ago, and got a similar answer. I guess the MAIN problem is trying to decide when varchar is no longer more efficient than TEXT. I saw a formula for how a varchar is stored but I didn't sit down and ponder it. Is there a rule of thumb, like, after [256,000] characters it is better to use a TEXT? (btw, if you want to answer my question along this line better, find it here: bit.ly/Ot2JJF, or just answer it here...) –  BillyNair Jul 10 '12 at 9:33
1  
Sooooooo, the only real difference is to duplicate the bounds-checking that should probably be in the program code anyway? –  Izkata Jul 10 '12 at 15:10
1  
@Izkata - There are implementation differences as well. It isn't about bounds checking, its about data type. A (US) zip code is always a 5 digit code, so using something like 'char' becomes part of the definition of this piece of data. If it was only stuff like bound checking we could all just use one data type for everything and do our checking and casting code side. –  System Down Jul 10 '12 at 15:57
2  
@SystemDown As far as I know, char, varchar, and text are all designed for storing the same type of data. So both answers here are about bounds checking. If there are efficiency differences, what are they? Why would I use varchar over text? –  Izkata Jul 10 '12 at 16:23
2  
@SystemDown Although storing postal codes as a char(5) may bite you if you start internationalizing. UK post codes vary in length and 5 characters is almost never enough. I don't know if the space in a UK post code is relevant for the parsing, though. –  Vatine Aug 5 '12 at 13:05

Databases are intensely concerned with performance--speed and minimizing storage. In most other parts of the computer world, you are not going to be bothered about how many characters are in your character string; it could be one, it could be the entire contents of an encyclopedia; it's all just a string. In fact, a lot of languages don't even bother you about whether it's a string or a number.

But as computers get faster and gain more memory, people put more data into their databases and do fancier queries. For a database CPU and memory are just as limiting today as they were in the days of 64Kb main memory and 10Mb hard drives (on mainframe computers).

A fixed number of bytes is a lot easier to deal with than a variable length number. 10 bytes is a lot easier to deal with than 1,000,000. So your database wants you to give it a clue so it can give you a gigabyte of results from terrabytes of data in microseconds. If you're not using your database that hard, you won't need the speed it's offering and will be annoyed at the needless questions. But if you do need the performance, you'll be happy to give it some hints.

As noted in the other answers, use char if it always uses a certain number of characters, varchar if the length can vary but it doesn't get too large (my guess is most DB's treat it as a char or text depending on size), and text if it could be any length. If your SQL tries to use a text column, it might be best to summarize it somehow and put it in a char or small varchar column also, then do where's and order by's on that. Of course, that's only if performance matters to you.

share|improve this answer

Sure. char shows the intent that the field will always have the same number of characters- i.e. an ISRC is always 12-characters long, char(12) is pretty close to that (although you'd need to add a CHECK constraint anyway to ensure ISRCs are valid), but it makes sense to choose a type closer to what you need.

varchar checks length out of the box, so that's better than text if you require that.

share|improve this answer

Char is indeed for fixed length but in the background a char(10) is really no different to a varchar(10) as it will reserve the same amount of space.

I would generally use char for single character storage and varchar for anything longer than one character.

Also worth noting is that chars are incapable of holding NULL values so in the background are converted to varchars is NULL is used on them.

share|improve this answer

If you're storing fixed length data, you should use a char. It'll probably be more performance and save you some space (very little) If you're storing variable length text, you should go with varchar if it is small enough. Use text if you can't use anything else.

Do not use char if you have variable length text. Something like

Insert (column, 'test') into table;
Select * from table where column = 'test';

Will probably return nothing if row is a char of size > 4 because it will be stored as 'test '. DB will pad on insert but not necessarily on select... Also, If you have a unique constraint on column, you won't be able to store one row with 'text' and own row with'text ' because of the padding

Varchar will not do padding, instead, it will reserve some more space to store the size of the string you're storing.this way, the previous request will work no matter what. Also you'll be able to store both 'text' and 'text ' even with a unique index

As for text, it works like a varchar except that the sorting and grouping on those might only work for the first n characters of the string. (you can probably configure n somewhere). Also I wouldn't be surprised if operations like 'upper' or even 'like awere just not available for text columns...

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.