Let me start by saying I am not against using Varchar (MAX). I love the idea of it, it has greatly simplified the use of LOB data types in my opinion. I am simply listing out some points I used recently to not use Varchar (MAX) . I recently reviewed a DB structure and found that ALL the Varchar fields were set to Varchar (MAX). Things like Name, Username(I would be very interested to see a 8GB username), City, etc… I’m not one to tell someone though you have to change your design “just because” I said so. I wanted some reasons. I posted the question on Twitter and looked around online for some good reasons here are the ones I ultimately ended up using.
1. Adding a Text Data type / Varchar (MAX) to a table removes the ability for online re-indexing. --Thanks @paulRandal
2. Performance Degradation. This goes back to another point about only get what you need. Their was Varchar(Max) on almost every table adding overhead to that many tables causes Performance Pain. Thanks @AaronThehobt for pointing that out.
3. Poor Design Practice. In general you should only be grabbing what you need. Creating a field that can store just about any amount of data just to hold 100 characters is simply a wasteful operation. I would suggest finding out what the business need/ logical need for the field and then use that to define the structure. Thanks @peschkaj for bringing the design aspect up.
So hopefully this will help you to convince someone else in the future to avoid using this as a catch all for any string based field. Feel free to add comments with other suggestions for reasons NOT to use it.
