(As someone has re-awakened this subject...)
Jeff Moden (8/23/2010)
There is sometimes a HUGE performance penalty (2:1) to be paid for using VARCHAR(MAX) depending, of course, on what you're doing. Correct sizing is always important. I'd dare say the same would be true for most any language... even .Net.
I was talking about local scalar variables, not arrays or database fields where extra microseconds can get multiplied by millions of records.
But I am curious as to why a VARCHAR(MAX) string that happens to contain say 100 chars is so much slower that a VARCHAR(100) that does? Its index and length values will have to be 32 bit numbers, but with 32/64 bit CPUs anything shorter tends to be less rather than more efficient anyway. As even a VARCHAR(100) is stored as a variable length string, I would not imagine the memory management issues are much different either. Or are they?
That said, I do always use the smallest VARCHAR I can - though I'm not sure whether it really makes much difference as SQL Server seems to allocate space for the actual content not the potential maximum content, as I tested by creating a large table of VARCHAR(4096) containing just 'x' (1 character) per record.