when to use varchar(max)?

When to use the 'max' keyword in the varchar or nvarchar data type?

Following are the points to consider while making the decision:

Without 'max':

- The max storage requirement for your field is fixed.

- The storage space is allocated whether you use it or not, resulting in unused bytes across the db.

- Performance could be better as the db need not do additional processing to determine data size (unlike the case of using 'max').

Using 'max':

- The storage requirement for your field need not be predefined (max permitted size depends on the DB though)

- DB allocates only the amount of space required by the data stored in the data type and increases space as needed. This is achieved by the DB internally through use of pointers and TEXT datatype to dynamically allocate and conserve space.

- Through max, you keep the business rule(related to data length) away from the data store, thereby allowing flexibility in business rule as far the data storage requirement is concerned. If you need to change the max length of the phone number or the first name a couple of years into your live app, you can just tweak that constant variable in your business logic to get it done, instead of trying to change the attribute of a table column and dependent changes(assuming your db allows that).

- Your may have performance issues due to the additional intelligence that db applies dynamically to calculate the space required.

Overall, it's the difference between the cost of storage and processing. It's interesting, how much work has gone behind supporting the keyword 'max' that started from SQL Server 2008. Kudos to the engineers!

Related articles

• http://stackoverflow.com/questions/148398/are-there-any-disadvantages-to-always-using-nvarcharmax
• http://social.msdn.microsoft.com/forums/en-US/sqlgetstarted/thread/4d9c6504-496e-45ba-a7a3-ed5bed731fcc/