Strategy for storing an string of unspecified length in Sql Server?

So a column will hold some text that beforehand I won't know how long the length of this string can be. Realistically 95% of the time, it will probably be between 100-500 chars, but there can be that one case where it will 10000 chars long. I have no control over the size of this string and never does the user. Besides varchar(max), what other strategy have you guys found useful? Also what are some cons of varchar(max)?

From stackoverflow jdelator

• Varchar(max) in sqlserver 2005 is what I use.

  SqlServer handles large string fields weirdly, in that if you specify "text" or a large varchar, but not max, it stores part of the bits in the record and the rest outside.

  To my knowledge with varchar(max) it goes ahead and stores the entire contents out of the record, which makes it less efficient than a small text input. But its more efficient than a "text" field since it does not have to look up that information 2 times by getting part inline and the rest from a pointer.

  JustinD : I might be wrong, but i was under the impression that it only stores outside the row if it goes over 8000 bytes and has to implicitly convert the data to a LOB and then stores a pointer to the actual data inline and the data outside
  From DevelopingChris

• Have you considered using the BLOB type?

  Also, out of curiosity, is you don't control the size of the string, and neither does the user, who does?

  jdelator : this is metadata from files
  From Aston

• nvarchar(max) is definitely your best bet - as i'm sure you know it will only allocate the space required for the data you are actually storing per row, not the actual max of the datatype per row.

  The only con i would see would be if you are constantly updating a row and it is switching from less than 8000 bytes to > 8000 bytes often in which case SQL will change the storage to a LOB and store a pointer to the data whenever you go over 8000 bytes. Changing back and forth would be expensive in this case, but you don't really have any other options in this case that I can see - so it's kind of a moot point.

  jdelator : I forgot to probably mention that this column will never get updated.
  From JustinD

• One inelegant but effective approach would be to have two columns in your table, one a varchar big enough to cover your majority of cases, and another of a CLOB/TEXT type to store the freakishly large ones. When inserting/updating, you can get the size of your string, and store it in the appropriate column.

  Like I say, not pretty, but it would give you the performance of varchar for the majority case, without breaking when you have larger values.

  From skaffman