SQL Server Text type vs. varchar data type:

Usually of thumb, should you ever need you text value to exceed 200 figures And don't use join about this column, use TEXT.

Otherwise use VARCHAR.

Presuming my data now's 4000 figures AND i don't use join about this column. With that quote, it's more beneficial to make use of TEXT/varchar(max) in comparison to presenting varchar(4000).

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

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

I disagree using the 200 factor since it is not described, unless of course it connect with the deprecated "text in row" option

  • In case your information is 4000 figures then use char(4000). It's fixed length
  • Text is deprecated
  • BLOB types are reduced

In old versions of SQL (2000 and earlier?) there is a max row period of 8 KB (or 8060 bytes). Should you used varchar for many lengthy text posts they'd be incorporated within this length, whereas any text posts wouldn't, so that you can keep more text consecutively.

This problem continues to be labored around in additional recent versions of SQL.

This MSDN page includes the statement:

SQL Server 2005 supports row-overflow storage which allows variable length posts to become pressed off-row. Merely a 24-byte root is saved in the primary record for variable length posts pressed from row because of the, the effective row limit is greater compared to previous releases of SQL Server. To learn more, begin to see the "Row-Overflow Data Exceeding 8 KB" subject in SQL Server 2005 Books Online.