Databases: Are "TEXT" fields less efficient than "varchar"?

Question

Is it less efficient to use TEXT than varchar in an SQL database?

If so why?

If not why would you not just always use TEXT?

I'm not targetting a specific database here but oracle is probably the most relevant, although I'm testing on MySQL for the time being as part of a proof of concept.

Answer 1

From Microsoft here

ntext, text, and image data types will be removed in a future version of Microsoft SQL Server. Avoid using these data types in new development work, and plan to modify applications that currently use them. Use nvarchar(max), varchar(max), and varbinary(max) instead.

When you use varchar(max) over text you can use it in the WHERE clause, because they work the same as their smaller counterparts, varchar,nvarchar and varbinary. Below is a small list of what should be used as opposed what was to be used:

• Use varchar(max) instead of text
• Use nvarchar(max) instead of ntext
• Use varbinary(max) instead of image

Answer 2

The short answer is: Yes, they are less efficient.

The longer, more convoluted answer is:

Yes, they are probably less efficient. It depends on which DBMS you are using and the size of your table, etc, etc. TEXT fields are variable width, and as such the DBMS has to do more work when trying to find records. How much this impacts on your performance is directly proportional to how efficient your DBMS is in general, how much data it stores about table rows, and whether it optimises fixed length tables.

I know MySQL works faster with fixed length table rows, but you have to tell it that the table is able to be treated as a fixed length table first. I don't really have any practical experience with other DBMS's to be able to relate actual numbers. But on tables with lots (reads a million or more) of records, it can make a significant difference. Smaller tables will have little to no practical difference though.

Answer 3

You need to be specific about which database you're talking about. I believe in at least some databases, TEXT is stored as a CLOB separate from the table itself (which just contains a reference). This leads to the table being smaller (good) but an extra lookup and probably cache miss when fetching (bad).

There are probably indexing and querying implications too, but again it will depend on the particular RDBMS you're using.

Answer 4

PostgreSQL documentation says:

Tip: There are no performance differences between these three types, apart from increased storage size when using the blank-padded type, and a few extra cycles to check the length when storing into a length-constrained column. While character(n) has performance advantages in some other database systems, it has no such advantages in PostgreSQL. In most situations text or character varying should be used instead.