nvarchar (max) length test in SQL

nvarchar (max) length test: Text length can exceed the upper limit of 8000 after using convert to force type conversions.
and the maximum length of nvarchar (max) can reach 2^31
The following is the validation sql:

Declare @A nvarchar (max) set @a=replicate (' * ', 9000);p rint len (@A) Set @a=replicate (CONVERT (nvarchar (max), ' * '), 9000); Print Len (@A) Set @a=replicate (convert (nvarchar (max), ' 1 '), POWER (2,26));p rint len (@A) Set @a=replicate (CONVERT ( nvarchar (max), ' 2 '), CAST (POWER (2,30) as bigint) * *);p rint len (@A)

However, the length of max in the table is not so long, the general limit is 4000/8000, the specific value is estimated to rely on SQL Server version. Here is an example:

CREATE table A (data nvarchar (max)) declare @a nvarchar (max) Set @a = REPLICATE (' A ', 8000) + ' B ';--delete from Ainsert to A values (@a) SELECT * from a

In fact, the data returned by select is only 8000 characters and B is not stored in the database.

So it's best not to rely on nvarchar (max) when you want to store more than 4000/8000 of your data, and you can consider ntext.

Discovery: convert (nvarchar (2), ' ABCDE ') is a type-conversion process, not a data-store process, so there is no error due to insufficient space size.
When you insert ' ABCDE ' into a field of type nvarchar (2), you are prompted to truncate the string, because the length of this field is defined as the length of the 2 Unicode encoding to be stored in both English and Chinese. Space is not enough, so error.

nvarchar (max) length test in SQL