41

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. ^_-)

Oded
  • 53,734
Izkata
  • 6,118
  • 7
  • 30
  • 44

3 Answers3

38

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 these types are internally saved using the same C data structure..

In MySQL

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

Just these two examples can be extrapolated to the other SQL RDBMS systems and should be reason enough to understand when to choose one type over the other.

Just to make it implicitly clear, you should never use TEXT as it is proprietary and non-standard. Any SQL you write against it will not be portable and will guaranteed to cause you problems in the future. Only use types that are part of the ANSI Standard.

  • Use CHAR when you know you have a fixed number of characters for every entry.
  • Use VARCHAR when you have a variable number of characters for every entry.
  • If you need more storage than VARCHAR can provide, CLOB with UTF-8 encoding or equivalent standard type.
  • NEVER use TEXT as it is non-standard.
13

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.

System Down
  • 4,763
5

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.

RalphChapin
  • 3,320