When is nvarchar(Max) = nvarchar(4000)?

Question

There are multiple posts on both SO and other sites which clearly state that the maximum length of nvarchar(max) is 2GB. However, I see also much confusion in both internet and real life that it actually is 8000/4000 in Unicode.

I would like to know what things could change that fact, or maybe lead someone to falsely assume so.

Some suggestions/partial answers I've already gathered:

1. Are there older SQL Server versions which did only support a maximum of 4000?

2. When assigning nvarchar(max) variable/column to a concatenation of non-max-sized components, must we convert everything to nvarchar(max) explicitly? Here is something showcasing a strange example, where a text-returning function requires converting, whereas the N for the literal can be omitted:

   declare @s nvarchar(max) 
   select @s = convert(nvarchar(max), replicate('.', 8000)) + N'Hi!'
   select len(@s) -- returns 8003
   
   declare @s nvarchar(max) 
   select @s = replicate('.', 8000) + N'Hi!' 
   select len(@s) -- returns 4000
   
   declare @s nvarchar(max) 
   select @s = convert(nvarchar(max), replicate('.', 8000)) + 'Hi!' 
   select len(@s) -- returns 8003
   

3. Are there ways to disable the functionality? Does sp_tableoption @OptionName=large value types out of row or OBJECTPROPERTY(id,'TableTextInRowLimit') have anything to do with this?

   Clarification: My aim is not to use this functionality, but be aware of its existence, which may be indeed have been used by a higher privilege user that will prevent me from using the max size.

4. Any other points gladly welcome

Answer 1

A few points here, as I can't fit into a comment.

1. Yes. (n)varchar(MAX) was introduced in SQL Server 2005. Previously you had to make use of text, ntext and image for varchar(MAX), nvarchar(MAX) and varbinary(MAX). The old data type have been deprecated for a long time now and you should not be using them.
2. When combining data, data type precedence is used to work out the final data type. When lengths are involved, the combined values of the lengths are used (A varchar(10) and a varchar(100) concatenated would return a varchar(110). Note, however, that to achieve the usage of the MAX length, at least one string must be an (n)varchar(MAX). SELECT REPLICATE(N'A',3000) + REPLICATE(N'A',3000) AS S would return a 4000 character string. + (String Concatenation) (Transact-SQL) - Remarks:

   If the result of the concatenation of strings exceeds the limit of 8,000 bytes, the result is truncated. However, if at least one of the strings concatenated is a large value type, truncation does not occur.

3. Disable what functionality? The usage of (n)varchar(MAX)? Why? If you wanted to stop people using a data type stop them using (n)text and image. In all seriousness though, you can't stop the usage of a data type. Perhaps you could get "clever" with DDL triggers, but I advise against it.
   
   To answer the edit, sp_tableoption cannot be used to stop someone using a MAX length datatype no; my above point stands. To quote the documetation (sp_tableoption (Transact-SQL) - Arguments:

   Large value types out of row:
   1 = varchar(max), nvarchar(max), varbinary(max), xml and large user-defined type (UDT) columns in the table are stored out of row, with a 16-byte pointer to the root.
   
   0 = varchar(max), nvarchar(max), varbinary(max), xml and large UDT values are stored directly in the data row, up to a limit of 8000 bytes and as long as the value can fit in the record. If the value does not fit in the record, a pointer is stored in-row and the rest is stored out of row in the LOB storage space. 0 is the default value.
   
   Large user-defined type (UDT) applies to: SQL Server 2008 through SQL Server 2017.
   
   Use the TEXTIMAGE_ON option of CREATE TABLE to specify a location for storage of large data types.

4. Too broad for SO.