In my earlier article, I have listed the major difference between VARCHAR(MAX) and VARCHAR(N). Now we will see whether it is a good practice to use VARCHAR(MAX) instead of VARCHAR(1-8,000) everywhere and why.
In my opinion, using VARCHAR(MAX) always instead of VARCHAR(N) is not a good practice. My reasons are:
- You cannot create regular index on a VARCHAR(MAX) column. So, if you want a indexed string column then you have to go for VARCHAR(N).
- There is no direct way to restrict the length of the string stored in a VARCHAR(MAX) column. On the other hand, using VARCHAR(1-8,000), you can limit the string saved in the column. For example, if you want the first name of a person should be within 200 characters, then you can use VARCHAR(200). This will avoid having multiple validation in front end application level.
- Not using varchar with specific string length confuses the future programmers. It will be difficult for them to figure out the expected size of the string and may end in validation mistakes.
Conclusion
DO NOT use VARCHAR(MAX) just because it can be. Use it only if the data to be stored can be more than 8,000 bytes.
Reference
- About VARCHAR(N) & VARCHAR(MAX) at Microsoft Docs.