Grody to the VARCHAR(MAX)

This morning I was looking through a new vendor database to see exactly what I would be supporting and I stumbled upon a curious table.  This table seemed quite normal to the naked eye until I glanced upon the data types.  There were four, count them four fields set to VARCHAR(MAX) and two set to NVARCHAR(MAX).  First of all, why the inconsistency in the data types?  Maybe there is a legitimate reason, but I am not buying it.  If you need Unicode support provided by the NVARCHAR data type wouldn’t you need it across all fields?  I have only worked with a few databases that needed Unicode support and that was the case for them.  Maybe there is a case for mixed usage, but I do not understand it and obviously I was not part of their design team.

Now onto the bigger elephant in the room, why on earth would you have all of these fields set to the maximum storage amount of two gigabytes (1GB for NVARCHAR taking up 2GB of space).  Are you really storing that much data in six fields of EACH record?  Keep in mind that SQL Server stores records in 8K pages.  When you exceed that the data goes to an overflow page with a pointer so that SQL knows where to find the rest of the data.  I understand that it will not take up that much space if you are not actually storing that much data, but there is a potential here for these table to be problematic.  Granted this might be a good design for one field if it stores a large amount of text or notes, but six fields in the same record?  I looked at the length of the records currently entered for these six fields and I found nothing larger than 100 characters.  Overkill?  What do you think?

Enjoy!

 

Advertisements

About SQLGator

Florida Gator and Microsoft Data Platform MVP, Star Wars fanatic and is there anything else...oh yeah PS4! I am a geek and SQL Server DBA who dabbles with VMWare, there are other technologies greater than these? Not so fast my friend! I also love to travel to new and exotic places.

Posted on November 21, 2013, in Design and tagged , . Bookmark the permalink. Leave a comment.

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

%d bloggers like this: