Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

nvarchar(99) v nvarchar(max)

Posted on 2013-12-06
5
Medium Priority
?
430 Views
Last Modified: 2013-12-11
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
Comment
Question by:soozh
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 52

Assisted Solution

by:Carl Tawn
Carl Tawn earned 1000 total points
ID: 39700927
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
 

Author Comment

by:soozh
ID: 39701048
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
 
LVL 52

Assisted Solution

by:Carl Tawn
Carl Tawn earned 1000 total points
ID: 39701054
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
 
LVL 17

Assisted Solution

by:dbaSQL
dbaSQL earned 500 total points
ID: 39701856
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
 
LVL 70

Accepted Solution

by:
Scott Pletcher earned 500 total points
ID: 39706689
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

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

722 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question