title: Using VARCHAR(MAX) with string concatenation in SQL Server 2005
body: <p>A user of one of our databases is trying to submit an UPDATE query to a SQL Server 2005 database and the text is being truncated unexpectedly.</p>
  
  <p>The field being truncated is VARBINARY(MAX) and is used to store HTML text.</p>
  
  <p>The query is roughly:</p>
  
  <pre><code>UPDATE Story 
  SET mainText = CONVERT (VARBINARY (MAX), '[...5000 chars of text...]' 
      + char(47) + char(47)
      + '[...3000 chars of text...]'
      + char(47) + char(47)
      + '[...5000 chars of text...]') 
  WHERE storyId = 123456
  </code></pre>
  
  <p>What I found after some experimentation is that when I remove the string concatenation, the query works as expected and the field is not truncated.</p>
  
  <p>I was able to work around the limitation and keep the concatenation by wrapping each separate string in a CAST to VARCHAR(MAX), so there is an option if the user feels the use of char() is required.</p>
  
  <p>What I <em>think</em> is going on is, whenever the concatenation operator is used, there's an implicit conversion happening to VARCHAR and that implicit conversion seems to be limited to VARCHAR(8000) instead of VARCHAR(MAX). So, before the string is even sent to the CONVERT function, it's already being truncated down to 8000 characters.</p>
  
  <p>If I'm correct, is there a way to change that behavior?</p>
  
  <p>If there is no way to change the behavior, is there another way to deal with the issue besides CAST?</p>