TEXT is used for large pieces of string data. If the length of the field exceeed a certain threshold, the text is stored out of row.
VARCHAR is always stored in row and has a limit of 8000 characters. If you try to create a VARCHAR(x), where x > 8000, you get an error:
Server: Msg 131, Level 15, State 3, Line 1
The size () given to the type ‘varchar’ exceeds the maximum allowed for any data type (8000)
These length limitations do not concern VARCHAR(MAX) in SQL Server 2005, which may be stored out of row, just like TEXT.
Note that MAX is not a kind of constant here, VARCHAR and VARCHAR(MAX) are very different types, the latter being very close to TEXT.
In prior versions of SQL Server you could not access the TEXT directly, you only could get a TEXTPTR and use it in READTEXT and WRITETEXT functions.
In SQL Server 2005 you can directly access TEXT columns (though you still need an explicit cast to VARCHAR to assign a value for them).
TEXT is good:
VARCHAR is good:
By selecting here I mean issuing any queries that return the value of the column.
By searching here I mean issuing any queries whose result depends on the value of the TEXT or VARCHAR column. This includes using it in any JOIN or WHERE condition.
As the TEXT is stored out of row, the queries not involving the TEXT column are usually faster.
Some examples of what TEXT is good for:
Some examples of what VARCHAR is good for:
As a rule of thumb, if you ever need you text value to exceed 200 characters AND do not use join on this column, use TEXT.
Otherwise use VARCHAR.
P.S. The same applies to UNICODE enabled NTEXT and NVARCHAR as well, which you should use for examples above.
P.P.S. The same applies to VARCHAR(MAX) and NVARCHAR(MAX) that SQL Server 2005+ uses instead of TEXT and NTEXT. You'll need to enable large value types out of row for them with sp_tableoption if you want them to be always stored out of row.
As mentioned above and here, TEXT is going to be deprecated in future releases:
The
text in rowoption will be removed in a future version of SQL Server. Avoid using this option in new development work, and plan to modify applications that currently usetext in row. We recommend that you store large data by using thevarchar(max),nvarchar(max), orvarbinary(max)data types. To control in-row and out-of-row behavior of these data types, use thelarge value types out of rowoption.