Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

nvarchar(99) v nvarchar(max)

Posted on 2013-12-06
5
Medium Priority
?
435 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
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

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

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

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…

886 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