Personally I believe that VARCHAR(MAX) is a datatype of last resort.

To give an extreme example the longest possible telephone number is 15 digits so allowing for formatting I allow VARCHAR(20). This means that there are some boundaries on what can be inserted into the field.

VARCHAR(MAX) effectively moves boundaries to such an extent that they become irrelevant. I take the view that if the database does allow unrestricted data then at some point unrestricted data will get put into it.

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.

As far as indexing VARCHAR(MAX) it is possible to put a column which stores the CHECKSUM for the VARCHAR(MAX) and index that. Obviously this isn't a guaranteed match but is good enough to get within shooting distance of the correct records.