Well, I would agree that for a field like telephone number, or zip code, or maybe even address, a fixed VARCHAR(x) value makes sense.
But what about for a field like, say, description? Or, a field that contains a comma-delimited list of values, the values of which could be an arbitrary length?
Yes, I could provide a fixed length. But it seems to me like that's just asking for problems in the future. Let's say I decided that my field was going to store a comma-delimited list of values, and that right now, it seems to me like the number of values would likely not exceed 10, and the length of each value would likely not exceed 5. So, sure, it might make sense for me, right now, to put VARCHAR(60) as my length, lets say.
But two months down the line, maybe something has changed, and now it's possible to have 10 values, or the length of a given value might be actually 15. Now I have to go and update my column definition to allow for a greater VARCHAR(x) value - but not only that, I also have to update all of my procedures, both the select and update ones, to reflect the new x value, or else it would either truncate or lose data that was necessary.
David, can you elaborate on this?
The other things that strike me is that the "text in row" option may apply and also the maximum replicated text size (defaults to 64Kb).
And, Howard, can you elaborate on this?
Although Varchar(n) and Varchar(max) can be stored in exactly the same way when the actual length is < 8000, the optimiser will treat them differently to allow for the possibility of out-of-row values. So it can be faster to query varchar(n) columns.
These are the kind of things that I'd like to know about. Essentially, reasons why a VARCHAR(MAX) datatype would perform worse than a VARCHAR(x) one.