Menu
I've got a quick question that I can't find an answer to anywhere. I frequently need to convert one type of database to another, therefor I'm writing a program to convert MS SQL Server databases back and forth. The problem I'm having is that I can't declare an nvarchar variable with a max length of above 4000. I get,
"The size (6000) given to the parameter 'description' exceeds the maximum allowed (4000)."
Yet that is clearly defined as an nvarchar(6000) in the original database, at least I think so because max_length is 6000, if you use max max_length is -1, right? I know I could just use nvarchar(max) but if I'm writing software that converts databases I want to stay as true to the original as possible.
Was the nvarchar max limit changed recently or is it some setting that I've missed?
626
NVARCHAR(Max) is used to store very large character data. NVARCHAR(Max) can hold as much as 2GB of Unicode character data. The word NVARCHAR stands for national varying character.
The answers is: there is no different between nvarchar(7) and nvarchar(4000) in term of performance & storage size. There is an interesting thing is that: if you change nvarchar(7) or nvarchar(4000) to nvarchar(max). There is a difference in term of performance & storage size. Wow, Why is this happen?
varchar is allowed to have 8000 characters (8000 Bytes). Whereas nvarchar is allowed to have 4000 characters (8000 Bytes).
The key difference between varchar and nvarchar is the way they are stored, varchar is stored as regular 8-bit data(1 byte per character) and nvarchar stores data at 2 bytes per character. Due to this reason, nvarchar can hold upto 4000 characters and it takes double the space as SQL varchar.
The given size (6000) is in bytes where as when you give the length, it is in number of chars of unicode. The limit 4000 is because internal storage of nvarchar(xxxx) and nvarchar(max) is different. If you want more storage than 4000 char, use nvarchar(max).
56
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
