30 January 2012
by Rob Garrison
www.simple-talk.com
* Where appropriate, use VARCHAR(n) over VARCHAR(MAX) for reasons of good design if not performance benefits, and because VARCHAR(MAX) data does not compress
* Storing large strings takes longer than storing small strings.
* Updating an in-row VARCHAR(MAX) value from below 8,000 to over 8,000 will be relatively slow, but the difference for a single transaction will likely not be measurable.
* Updating an in-row VARCHAR(MAX) value from over 8,000 to below 8,000 will be faster than if the table is set to store data out-of-row.
* Using the out-of-row option for VARCHAR(MAX) will cause slower writes until the strings are very long.
Full article
http://www.simple-talk.com/sql/database-administration/whats-the-point-of-using-varchar%28n%29-anymore/