title: Converting ntext to nvcharmax(max) - Getting around size limitation
body: <p>I'm trying to change an existing SQL <strong>NText</strong> column to <strong>nvcharmax(max)</strong>, and encountering an error on the size limit. There's a large amount of existing data, some of which is more than the 8k limit, I believe.</p>
  
  <p>We're looking to convert this, so that the field is searchable in LINQ.</p>
  
  <p>The 2x SQL statements I've tried are:</p>
  
  <pre><code>update Table
  set dataNVarChar = convert(nvarchar(max), dataNtext)
  where dataNtext is not null
  
  update Table
  set dataNVarChar = cast(dataNtext as nvarchar(max))
  where dataNtext is not null
  </code></pre>
  
  <p>And the error I get is:</p>
  
  <pre><code>Cannot create a row of size 8086 which is greater than the allowable maximum row size of 8060.
  </code></pre>
  
  <p>This is using SQL Server 2008.</p>
  
  <p>Any help appreciated,
  Thanks.</p>
  
  <hr>
  
  <h2>Update / Solution:</h2>
  
  <p>The marked answer below is correct, and SQL 2008 can change the column to the correct data type in my situation, and there are no dramas with the LINQ-utilising application we use on top of it:</p>
  
  <pre><code>alter table [TBL] alter column [COL] nvarchar(max)
  </code></pre>
  
  <p>I've also been advised to follow it up with:</p>
  
  <pre><code>update [TBL] set [COL] = [COL]
  </code></pre>
  
  <p>Which completes the conversion by moving the data from the lob structure to the table (if the length in less than 8k), which improves performance / keeps things proper.</p>