title: The weird length of varchar and nvarchar in T-SQL
body: <p>I had a question about the Transcat SQL, below is the sql code;</p>
  
  <pre><code>DECLARE @main nVARCHAR(max);
  
  --there are over 4000 characters to @main 
  set @main = '01234567890123456789...';
  Print len(@main)
  </code></pre>
  
  <p>the length of @main is correct, e.g. 4007
  however, if I change the code to below:</p>
  
  <pre><code>DECLARE @main nVARCHAR(max);
  
  --there are over 4000 characters to @main 
  set @main = N'01234567890123456789...';
  Print len(@main)
  </code></pre>
  
  <p>the length of @main will always be 4000, it is weird, I don't understand.
  Another thing is if I change the code to below:</p>
  
  <pre><code>DECLARE @main nVARCHAR(max), @split nVARCHAR(500);
  
  --there are 500 characters to @split 
  set @split = '01234567890123456789...';
  set @main = @split + @split + @split + @split + @split + @split + @split + @split + @split + @split;
  Print len(@main)
  </code></pre>
  
  <p>The length of @main is 4000, why? No matter I add letter N before '01234567890123456789...'?
  if I change the @split to varchar(500)
      DECLARE @main nVARCHAR(max), @split VARCHAR(500);</p>
  
  <pre><code>--there are 500 characters to @split 
  set @split = '01234567890123456789...';
  -- 10 @split
  set @main = @split + @split + @split + @split + @split + @split + @split + @split + @split + @split;
  Print len(@main)
  </code></pre>
  
  <p>The length of @main is correct, no matter I add letter N before ''01234567890123456789...' or not, the length of @main is always 5000</p>