Database – Reasons to Use VARCHAR Over TEXT Columns

databaseefficiencyMySQLperformancepostgres

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

Best Answer

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.
Related Topic