Regarding SQL Server, I understand :
var means the memory is lazy allocated, meaning it fits to the data exactly (on insertion).
MAX means there is no size restriction\limitation.
Then, is it always preferable to use MAX when using varchar, as we don't allocate the whole size anyhow?
Should we use a constant size only if there is a constraint we want to enforce on this DB column?
There is a very good article on this subject by SO User @Remus Rusanu. Here is a snippit that I've stolen but I suggest you read the whole thing:
The code path that handles the MAX types (varchar, nvarchar and varbinary) is different from the code path that handles their equivalent non-max length types. The non-max types can internally be represented as an ordinary pointer-and-length structure. But the max types cannot be stored internally as a contiguous memory area, since they can possibly grow up to 2Gb. So they have to be represented by a streaming interface, similar to COM’s IStream. This carries over to every operation that involves the max types, including simple assignment and comparison, since these operations are more complicated over a streaming interface. The biggest impact is visible in the code that allocates and assign max-type variables (my first test), but the impact is visible on every operation.
In the article he shows several examples that demonstrate that using varchar(n) typically improves performance.
You can find the entire article here.
Look here for a good answer:
Should I use varchar(n) or varchar(MAX)?
The short answer is that from a storage perspective it's the same, but from a query optimization perspective, it's better to use varchar(N).
Here's what microsoft recommends:
ref
Just to be quite explicit on this one, the answer is NO. It is always preferable to use varchar(N), and if you know the size will not vary, then char(N). The MAX types do not and cannot support most of the native SQL features so you cannot add indexes, perform joins nor do effective searches on those types. Incidentally, this is one reason why the full-text search capability exists in SQL Server.
In addition, varchar(max) prevents the ability to perform online indexes against the entire table which contains the varchar(max) field. This will significantly impact performance of your system.
Varchar(max) should only ever be used if the size of the field is known to be over 8K. In every other instance, the size must be specified. Failure to do so is poor design and will lead to performance issues on any but the most trivial of systems.
Some references:
