CODEWITHSUNDEEP
sql-serverdatabasetextvarchar
Pacerier
Pacerier

Reputation: 89763

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 varchar(4000).

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

Upvotes: 1

Views: 905

Answers (3)

Jon Egerton
Jon Egerton

Reputation: 41579

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.

Upvotes: 0

gbn
gbn

Reputation: 432667

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

Upvotes: 2

Mitch Wheat
Mitch Wheat

Reputation: 300789

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

Upvotes: 4

Related Questions