• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 444
  • Last Modified:

nvarchar(99) v nvarchar(max)

Hello,

I have always designed tables using columns that are set to the size i needed.  

However i have been told by a developer that we should just use nvarchar(max) to avoid any problems with column sizes in the future.

What is the thinking here.  Is it a large overhead to use nvarchar(max) on everything?

/r
0
soozh
Asked:
soozh
4 Solutions
 
Carl TawnSystems and Integration DeveloperCommented:
It depends on how much data you are actually planning to store in it. nvarchar will only allocate the space it requires, rather than grabbing space for the potential maximum size.

But on the other hand, data pages work on 8060 byte boundaries so if you're total overall row size extends beyond that then SQL will move it to a ROW_OVERFLOW_ALLOCATION unit and add a pointer to it in the main page. That creates an overhead when retrieving the row. If your rows will always be over 8060 bytes then it doesn't matter because you will be doing this already. But, if you size the NVARCHAR to prevent going over the 8060 mark then you'll save on the overhead.
0
 
soozhAuthor Commented:
This issue has arisen because we have an email address column, and its currently 50 chars.  

The user now wants 100 chars because they have one email address that is 51 characters long.

The developer says use nvarchar(max) so we dont ever have this problem again.

But it seems to be using a sledgehammer to crack a walnut.
0
 
Carl TawnSystems and Integration DeveloperCommented:
I'd agree. If you know roughly the maximum length of data for the field then stick with it. Using MAX otherwise is just lazy.
0
 
dbaSQLCommented:
Agree with the other Expert, but I want to note, there are definite disadvantages of NVARCHAR(MAX).  See this post, there are several good examples of why you may want to avoid MAX:  

http://stackoverflow.com/questions/148398/are-there-any-disadvantages-to-always-using-nvarcharmax

In my book, I always think it's best to define the datatype and datalength as close to the actual data as possible.  If you know what you will be storing, you should define the column appropriately.  If you don't know what you'll be storing, I would spend some time trying to figure it out, or I would use a non-MAX attribute, as close to the datalength as possible.
0
 
Scott PletcherSenior DBACommented:
If you don't require nvarchar(max), don't use it.  

1) (max) columns have restrictions that don't apply to (nnn) columns.

  For example:
  *) (max) columns cannot be used as a key column in an index; this could severely hurt
  performance, or even cripple searches, if the index is needed.
  *) (max) columns cannot be used in certain cross-server tasks.

2) depending on table settings, (max) could cause huge overhead by forcing all such data to separate rows, whether anywhere close to 4000 nvarchar (or 8000 varchar()) bytes or not.

That developer may be great, even brilliant, as a developer, but he/she is not a DBA :-) .
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now