Large Value Data Types (Varchar(max) vs Text)

Have you wondered which data type to use when you are assigned to store large size data in a table (larger than 8000 characters). Physical limitation for SQL Server is 8 K per row. To store anything larger than 8 K you will need to choose between Text and varchar(max). On the surface both look same but there is a fundamental difference.
First of all Text is legacy type and existed before SQL 2005 release. Text are always stored outside the table as LOB object. It can store up to 2 GB of data. But it has few shortcoming listed below..

- Cannot join on text column
- Cannot be used on where clause
- String manipulation is limited (sub string functions)

With SQL 2005 Varchar(max) and nVarchar(max) was introduced. It addresses the limitation of text in terms of join, search and string manipulation with out compromising on the size limit ( 2 GB).Also varchar(max) is stored in the table space when size is below 8 K, if size exceeds 8 K it is stored outside table space. Data retrieval from tablespace is much faster.According to Microsoft Text data types will be depricated from future releases. Avoid using this feature in new development work, and plan to modify appliation that currently use Text and nText types.
Naturally Varchar(max) is a better option and should be used when ever possible. Text data types should only be for backward compatibility.
For additional reading on Large Value Data Types
http://msdn.microsoft.com/en-us/library/ms17815(v=SQL.110).aspx