Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How does SQL Server store more than 4000 characters in NVARCHAR(max)?

I have defined a stored procedure that has a parameter of type NVARCHAR(max) and I process that string in my stored procedure. I know that max of nvarchar is 4000. But I have passed a string with 5700 characters to my sp with no errors. Is it possible?

like image 527
Bobs Avatar asked Dec 25 '12 12:12

Bobs


People also ask

How is Nvarchar Max stored?

You can see that: If we use nvarchar(max) your data will be stored in LOB_DATA instead of IN_ROW_DATA or ROW_OVERFLOW_DATA. By default, all rows are stored in IN_ROW_DATA, which give you a great performance.

How increase Nvarchar size in SQL Server?

You can't create a NVARCHAR(8000). If you need to go beyond 4,000 characters with the NVARCHAR data type, you need to use NVARCHAR(max).

Does Nvarchar Max waste space?

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).


1 Answers

Yes it is possible - according to the MSDN documentation:

nvarchar [ ( n | max ) ]

Variable-length Unicode string data. n defines the string length and can be a value from 1 through 4,000. max indicates that the maximum storage size is 2^31-1 bytes (2 GB). The storage size, in bytes, is two times the actual length of data entered + 2 bytes. The ISO synonyms for nvarchar are national char varying and national character varying.

So if you specify nvarchar(max) you can store up to 1 billion 2-byte Unicode characters. That's Leo Tolstoj's War and Peace well over a hundred times over ....

SQL Server stores those max columns into special structures internally, which makes it possible to get around the 8K limit of the SQL Server pages. It works - but it's more effort than just storing a few hundred bytes on a page, so this storage system does pose more strain on SQL Server - use it with care, use it only when you really need to (and most definitely don't just make all your columns (n)varchar(max), just because you're lazy!)

Check out this really good article on Simple Talk: What's the Point of Using VARCHAR(n) Anymore? - it explains very nicely how (max) datatypes are different and less suited for smaller strings - use only when really needed!

like image 82
marc_s Avatar answered Oct 31 '22 03:10

marc_s