Reasons for NOT using Varchar (MAX)

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. 

Advertisements

2 responses to “Reasons for NOT using Varchar (MAX)

  1. I agree with you that you should only use varchar(MAX) very sparingly, if at all. The only instance I can see to use it, one that I am running into, is a 'Description' field. Some descriptions tend to get quite long, and I worry that putting any kind of restriction could potentially cause problems. But, other than a situation like that, it should really not be used at all.

  2. you can open any search engine and get to know more about the way of recovery tool box for access, provided by some applications

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s