title: Maximum size of a varchar(max) variable
body: <p>At any time in the past, if someone had asked me the maximum size for a <code>varchar(max)</code>, I'd have said 2GB, or looked up a more exact <a href="http://msdn.microsoft.com/en-us/library/ms176089.aspx" rel="noreferrer">figure</a> (2^31-1, or 2147483647).</p>
  
  <p>However, in some recent testing, I discovered that <code>varchar(max)</code> variables can apparently exceed this size:</p>
  
  <pre><code>create table T (
      Val1 varchar(max) not null
  )
  go
  declare @KMsg varchar(max) = REPLICATE('a',1024);
  declare @MMsg varchar(max) = REPLICATE(@KMsg,1024);
  declare @GMsg varchar(max) = REPLICATE(@MMsg,1024);
  declare @GGMMsg varchar(max) = @GMsg + @GMsg + @MMsg;
  select LEN(@GGMMsg)
  insert into T(Val1) select @GGMMsg
  select LEN(Val1) from T
  </code></pre>
  
  <p>Results:</p>
  
  <pre><code>(no column name)
  2148532224
  (1 row(s) affected)
  Msg 7119, Level 16, State 1, Line 6
  Attempting to grow LOB beyond maximum allowed size of 2147483647 bytes.
  The statement has been terminated.
  
  (no column name)
  (0 row(s) affected)
  </code></pre>
  
  <p>So, given that I now know that a <em>variable</em> can exceed the 2GB barrier - does anyone know what the actual limit is for a <code>varchar(max)</code> variable?</p>
  
  <hr>
  
  <p>(Above test completed on SQL Server 2008 (not R2). I'd be interested to know whether it applies to other versions)</p>