On using NVARCHAR(MAX)

Question

I have a database in which I have a lot of string columns. Should I use NVARCHAR(MAX) or instead restrict the strings to the maximum they can reasonably be such as NVARCHAR(100) etc? What I really want to know is if I have a column set to NVARCHAR(MAX) and all the values for that column are 100 characters or less then would I save space by setting that column to NVARCHAR(100)?

I know spac is not the only issue here and that "validation" of data is also important but I'm just asking about the space issue.

You should restrict the length of the column to the maximum possible/reasonable (depending on circumstances) length of the data you intend to put in there to stop incorrect data from being placed in your database. What that limit is is up to you; space should not be a consideration. — Ben, Feb 15 '13 at 10:46
You make a very valid point - I was just interested in knowing about the space issue that is all. — Sachin Kainth, Feb 15 '13 at 10:47

score 5 · Answer 1 · edited May 23 '17 at 10:28

Did you know you cannot index a column of type nvarchar(max) ? So you will not be able to optimize queries on this column. No size impact, agreed, but a potential perf impact for sure.

Also note that after a certain size the column is stored out of row, so fetching it will add an extra perf hit.

If you are worried about getting errors at insert time, the general solution is either:

validate client side
trim the string

Here is a question that discusses this in more detail.

score 2 · Accepted Answer · answered Feb 15 '13 at 10:48

It will not save space, as the size of an nvarchar field depends on the size of the data entered, not the field definition. As per the documentation.

The storage size, in bytes, is two times the actual length of data entered + 2 bytes

The reason you would set a size limit is to validate that the data is that size or less, because that is what your logical data-model stipulates.

On using NVARCHAR(MAX)

2 Answers2