Confused about the limit of nvarchar

Question

I have a quick question that I can not find the answer anywhere. I often need to convert one type of database to another, so I write a program to convert MS SQL Server databases back and forth. The problem I am facing is that I cannot declare an nvarchar variable with a maximum length above 4000. I get

"The size (6000) specified by the description parameter exceeds the maximum allowable size (4000)."

However, it is clearly defined as nvarchar (6000) in the source database, at least I think so because max_length is 6000, if you use max max_length is -1, right? I know that I can just use nvarchar (max), but if I write software that converts databases, I want to keep the original as soon as possible.

Has the nvarchar max limit been changed recently or is it some kind of setting that I missed?

Answer 1

This size (6000) is in bytes, where when you specify the length, this is the number of Unicode characters. The limit of 4000 is that the internal storage of nvarchar (xxxx) and nvarchar (max) is different. If you need more memory than 4000 char, use nvarchar (max).