As far as I know, VARCHAR(MAX) is essentially the replacement for the old TEXT data type.
My question is - does VARCHAR(MAX) perform the same way as VARCHAR(x)? IE, does it allocate only enough space to a row to contain the value of that field + 2 more to handle the size? And, for that matter, am I even interpreting VARCHAR(x) correctly?
To my knowledge, CHAR(x) means that the given item requires x bytes of space, regardless of what is stored, meaning even if the field is empty, it still takes up x bytes of space - though if the field is NULL, it takes no space.
VARCHAR(x) on the other hand, means that the given item takes only as much space as its length, + 2 to indicate what the length is - to a maximum of x + 2. Again, if the field is NULL, it takes no space.
VARCHAR(MAX), then, by my reasoning, also takes up only enough space to fit the item in question, + 2 for the length, and, once again, no space for a NULL value.
If these are all true, then what is the purpose of using VARCHAR(x) instead of VARCHAR(MAX)? The only advantage I can see, is that VARCHAR(MAX) can't be indexed using a normal index, while VARCHAR(x) can, as long as the size of the index is less than 900 bytes.
So, as far as I know, the only reason to use VARCHAR(x) is if the field is intended to be indexed. If not, there's no reason not to use VARCHAR(MAX).
Is this reasoning correct?