1

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?)

Community
  • 1
  • 1
Pacerier
  • 86,231
  • 106
  • 366
  • 634
  • Does it matter? These days, I would say that you should be using VARCHAR(MAX) instead of TEXT anyway. Are you talking about a particular version of SQL Server? – Matt Gibson Aug 15 '11 at 09:45
  • @Matt Gibson heys i've added that varchar(max) part in. i'm not talking about a particular version, but if that's required, 2008 – Pacerier Aug 15 '11 at 09:49

3 Answers3

4

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

Mitch Wheat
  • 295,962
  • 43
  • 465
  • 541
2

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
gbn
  • 422,506
  • 82
  • 585
  • 676
  • I assume he didn't mean "my data is always exactly 4000 characters" but he meant that it can be up to 4000 chars. If so I wouldn't advise char(x). Also if by "BLOB types" you mean "image" then this is deprecated too. – Nux Aug 29 '11 at 10:59
  • @Nux: BLOB types includes the max types too – gbn Aug 29 '11 at 11:02
0

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.

Jon Egerton
  • 40,401
  • 11
  • 97
  • 129