Sql – Use SQL varchar(max) or text

sqlsql-server-2005

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?

Best Answer

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

Related Topic