Solved

nvarchar(99) v nvarchar(max)

Posted on 2013-12-06
5
410 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 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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Safely Uninstall SQL Server 2008 R2 Express 3 60
CROSS APPLY 4 45
Solution for warm standby SQL server 20 41
Where to download and how to install sqldmo.dll 5 36
Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
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.
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
As a trusted technology advisor to your customers you are likely getting the daily question of, ‘should I put this in the cloud?’ As customer demands for cloud services increases, companies will see a shift from traditional buying patterns to new…

864 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

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now