Server Fault is a question and answer site for system and network administrators. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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

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.


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


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


  • 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


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.