4

I'm using SQL Server 2005 and I have a column where I need to store large amounts of text (sometimes over 8000 characters, the varchar limit). Is there a disadvantage to using the "text" data type? I also read about using varchar(MAX) -- would that be better if most of my data stored in there was less than 8000 characters, but I needed to be able to support more?

Austin
  • 205
  • 3
  • 5

5 Answers5

14

You should always go for the new LOB types in 2005 instead of the legacy types (text, ntext, image) as long as you have the potential for data over 8000 bytes.

The new types work with the majority of the intrinsic string-manipulation functions, whereas the legacy types do not. They are stored in the database in the same exact way, but there are some small tweaks to read algorithms for the new types too.

There are some things to be aware of though:

  • legacy types are stored off-row by default, which means there's a random-IO to get to the data regardless of data size. You can of course change this with the 'text in-row' option
  • new types are stored ON row by default, up to an 8000-byte limit and as long as there's space in the record. This reduces the chance of taking that random-IO to get to the value BUT makes your data records much longer leading to other problems. You can change whether data is stored in-row or out-of-row by changed the large-values-types-off-row option
  • any time you have either a new or legacy LOB type in your table definition means that the table's clustered index cannot make use of online index operations in Enterprise edition.

I posted a blog post that discusses this in more detail at http://www.sqlskills.com/BLOGS/PAUL/post/Importance-of-choosing-the-right-LOB-storage-technique.aspx.

Hope this helps

Paul Randal
  • 7,194
  • 1
  • 36
  • 45
2

Use varchar(max), it's the recommended approach going forward, so it will save you upgrading problems, it's also much easier to work with then the text data type.

Bravax
  • 511
  • 3
  • 12
1

Use varchar(MAX). The limit for varchar(MAX) is 2GB.

If the content is less than 8,000 bytes it will be stored inline. However, if the content is greater than 8,000 bytes, it gets stored in the LOB just like a text field.

Also, TEXT, NTEXT, and IMAGE data types are going to be deprecated at some point in the future.

Reference

Glorfindel
  • 1,213
  • 4
  • 15
  • 22
Jimmie R. Houts
  • 1,853
  • 1
  • 15
  • 12
0

A few good blog postings from MVP Simon Sabin on these at his blog. Search for Simon Sabin varchar(max)

I'd agree with Paul, use varchar(max) whenever possible.

Steve Jones
  • 795
  • 5
  • 8
0

varchar(max)

  • 2GB limit, or 2^31characters
  • if < 8000 bytes, it will be stored inline
  • if > 8000 stored using LOB

Text

  • 2,147,483,647 characters
  • This is being deprecated in favor of varchar(max)
  • Table stores the text value in a LOB structure and the table will only hold a pointer
Tom
  • 133
  • 8