what advantage does TEXT have over varchar when required length <8000?

SQL Server Text type vs. varchar data type:

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.

Assuming my data now is 4000 characters AND i do not use join on this column. By that quote, it is more advantageous to use TEXT/varchar(max) compared to using varch开发者_JAVA百科ar(4000).

Why so? (what advantage does TEXT/varchar(max) have over normal varchar in this case?)

TEXT is deprecated, use nvarchar(max), varchar(max), and varbinary(max) instead: http://msdn.microsoft.com/en-us/library/ms187993.aspx

I disagree with the 200 thing because it isn't explained, unless it relate to the deprecated "text in row" option

• If your data is 4000 characters then use char(4000). It is fixed length
• Text is deprecated
• BLOB types are slower

In old versions of SQL (2000 and earlier?) there was a max row length of 8 KB (or 8060 bytes). If you used varchar for lots of long text columns they would be included in this length, whereas any text columns would not, so you can keep more text in a row.

This issue has been worked around in more recent versions of SQL.

This MSDN page includes the statement:

SQL Server 2005 supports row-overflow storage which enables variable length columns to be pushed off-row. Only a 24-byte root is stored in the main record for variable length columns pushed out of row; because of this, the effective row limit is higher than in previous releases of SQL Server. For more information, see the "Row-Overflow Data Exceeding 8 KB" topic in SQL Server 2005 Books Online.