Any Disadvantages of using nvarchar(max)

Question

4.00/5 (1 vote)

See more:

SQL-Server-2008R2

I have columns with Characters Field in my Table
(eg colums: Name,Address,Father_Name,Emailaddres ..etc)

I have declared those column Datatype as Nvarchar(max)
instead of using nvarchar(100),nvarchar(255)

1) so does it affect my database.

2) Any DisAdvantages?

Posted 5-Dec-14 23:18pm

King Fisher

Add a Solution

4 solutions

Solution 4

hi,

if your are going to store more then size 8000 then you can use it,otherwise it effect on Performance.

When you store data to a VARCHAR(100) column, the values are physically stored in the same way. But when you store it to a VARCHAR(MAX) column, behind the screen the data is handled as a TEXT value. So there is some additional processing needed when dealing with a VARCHAR(MAX) value.

see the below

1. You can create an index on the fixed length column but can not create on the nvarchar(max) column. If you are using the column in where clause in your queries the you should always use fixed length column instead of nvarchar(max). This should be enough to use the nvarchar fixed lengthcolumn. I will specify others as well. However, index key length allowed is 900 bytes. Thus any nvarchar column which will exceed this value will cause issue and will be failed.
2. Memory grant could be a big issue where the server is already memroy starved.As expected row size is more the optimizer will
estimates more memory grant and this value will be much higher than actual required and this would be a waste of a resource as precisous as memory.
If you have couple of queries which are using nvarchar(amx) column and sorting is needed then server might have memroy related issues.
This could be a big perf issue.
3. You can not create the indexes online when the table or index has the nvarchar(max) column.
alter index all on nvarcharmax
rebuild with (online=on)

Posted 7-Dec-14 17:36pm

mudgilsks

Solution 2

hi,

if your are going to store more then size 8000 then you can use it,otherwise it effect on Performance.

Posted 5-Dec-14 23:32pm

mudgilsks

Comments

Maciej Los 6-Dec-14 7:13am

"it effect on Performance" - how?

mudgilsks 6-Dec-14 8:36am

When you store data to a VARCHAR(100) column, the values are physically stored in the same way. But when you store it to a VARCHAR(MAX) column, behind the screen the data is handled as a TEXT value. So there is some additional processing needed when dealing with a VARCHAR(MAX) value.

see the below

1. You can create an index on the fixed length column but can not create on the nvarchar(max) column. If you are using the column in where clause in your queries the you should always use fixed length column instead of nvarchar(max). This should be enough to use the nvarchar fixed lengthcolumn. I will specify others as well. However, index key length allowed is 900 bytes. Thus any nvarchar column which will exceed this value will cause issue and will be failed.
2. Memory grant could be a big issue where the server is already memroy starved.As expected row size is more the optimizer will
estimates more memory grant and this value will be much higher than actual required and this would be a waste of a resource as precisous as memory.
If you have couple of queries which are using nvarchar(amx) column and sorting is needed then server might have memroy related issues.
This could be a big perf issue.
3. You can not create the indexes online when the table or index has the nvarchar(max) column.
alter index all on nvarcharmax
rebuild with (online=on)

/\jmot 7-Dec-14 5:49am

move it to the answer.I think will be better. :)

Solution 1

All here..
http://stackoverflow.com/questions/148398/are-there-any-disadvantages-to-always-using-nvarcharmax[^]

Posted 5-Dec-14 23:22pm

/\jmot

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Accepted Answer · 2014-12-06T01:29:00

Solution 3

I'd say: generally - i don't see any disadvantages in using of NVARCHAR(MAX) data field.
I'd say: everything what you do in your life must be logically wellgrounded (reasonable) ;)

I'd answer this way: Is there any reason to store Name, Address, Father_Name, Emailaddres as NVARCHAR(MAX) data type[^]? Please, note that: nvarchar data type is variable-length unicode string data, but max indicates that the maximum storage size is 2^31-1 bytes (2 GB).

Posted 6-Dec-14 1:29am

Maciej Los

Comments

King Fisher 6-Dec-14 7:34am

thank you Maciej ;)

King Fisher 6-Dec-14 7:36am

you mean, its a bad table Design ?

Maciej Los 6-Dec-14 7:53am

Not necessary...
All what i wanted to say: it depends on many factors, where one of them are requirements.