Solved

nvarchar(99) v nvarchar(max)

Posted on 2013-12-06
5
424 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 250 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 250 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 125 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 69

Accepted Solution

by:
Scott Pletcher earned 125 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

[Webinar] Learn How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

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

After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

734 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