title: Efficiency of varchar(max) in T-SQL code
body: <p>I have a question about the "efficiency" (speed, memory usage) of <code>(n)varchar(max)</code> versus <code>(n)varchar(&lt;explicit-size&gt;)</code> <strong>in T-SQL code</strong> (stored procedures, functions).  I am <em>not</em> asking about its usage in column definitions.</p>
  
  <p>In existing code, I am noticing lots of occasions <em>along the lines of</em>, say:</p>
  
  <pre><code>CREATE TABLE [table] (
      [column] nvarchar(1000)
  )
  
  DECLARE @var nvarchar(2000)
  SELECT @var = [column] FROM  TABLE WHERE ...
  SET @var = @var + @somethingelse + @anotherthing ...
  SET @var = REPLACE(@var, N'search', N'lotstoreplacewith')
  ...
  </code></pre>
  
  <p>The gist being (the above is only an example) I am ultimately building longer strings off limited-size string columns. It is difficult/inconvenient to know and maintain just how big these strings could become.</p>
  
  <p>So, it would be simpler for me if I changed to declaring these T-SQL variables as <code>nvarchar(max)</code>. Then I do not have to worry about what maximum size they could reach. They would be similar to a programming language's "unlimited" string size.</p>
  
  <p>Does anybody know how "efficiently" SQL Server handles code which manipulates variables declared <code>(n)varchar(max)</code>? For example, does it reserve a huge amount of space for such a variable (I'm <em>hoping</em> it handles it purely dynamically), or for <code>CAST/CONVERT()</code>, or do string functions using it suffer some performance penalty? [If it is relevant, I have to support SQL Server 2008 R2 onward.]</p>
  
  <p>[EDIT: It has been suggested that my question is a duplicate of <a href="https://stackoverflow.com/questions/148398/are-there-any-disadvantages-to-always-using-nvarcharmax">Are there any disadvantages to always using nvarchar(MAX)?</a>. However, all but a single post there refer to <code>varchar(max)</code> in <em>column definitions</em>, and my question explicitly states I am asking about <strong>T-SQL code</strong> (variables etc.) performance. I am posting an answer (because it is large) to my question below, which draws on that post and extends it with some new information.]</p>