title: When is nvarchar(Max) = nvarchar(4000)?
body: <p>There are multiple posts on both SO and other sites which clearly state that the maximum length of <code>nvarchar(max)</code> is 2GB. However, I see also much confusion in both internet and real life that it actually is 8000/4000 in Unicode.</p>
  
  <p>I would like to know what things could change that fact, or maybe lead someone to falsely assume so.</p>
  
  <p>Some suggestions/partial answers I've already gathered:</p>
  
  <ol>
  <li>Are there older SQL Server versions which did only support a maximum of 4000?</li>
  <li><p>When assigning <code>nvarchar(max)</code> variable/column to a concatenation of non-max-sized components, must we convert everything to <code>nvarchar(max)</code> explicitly? Here is something showcasing a strange example, where a text-returning function requires converting, whereas the N for the literal can be omitted:</p>
  
  <pre><code>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
  </code></pre></li>
  <li><p>Are there ways to <em>disable</em> the functionality? Does <code>sp_tableoption @OptionName=large value types out of row</code> or <code>OBJECTPROPERTY(id,'TableTextInRowLimit')</code> have anything to do with this? </p>
  
  <p><strong>Clarification</strong>: 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 <em>me</em> from using the max size.</p></li>
  <li><p>Any other points gladly welcome</p></li>
  </ol>