Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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?

share|improve this question

5 Answers 5

up vote 14 down vote accepted

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

share|improve this answer

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.

share|improve this answer

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

share|improve this answer

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.

share|improve this answer

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
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.