title: SQL Server, Converting NTEXT to NVARCHAR(MAX)
body: <p>I have a database with a large number of fields that are currently NTEXT.</p>
  <p>Having upgraded to SQL 2005 we have run some performance tests on converting these to NVARCHAR(MAX).</p>
  <p>If you read this article:</p>
  <p><a href="https://web.archive.org/web/20210125040904/http://geekswithblogs.net/johnsperfblog/archive/2008/04/16/ntext-vs-nvarcharmax-in-sql-2005.aspx" rel="nofollow noreferrer">http://geekswithblogs.net/johnsPerfBlog/archive/2008/04/16/ntext-vs-nvarcharmax-in-sql-2005.aspx</a></p>
  <p>This explains that a simple ALTER COLUMN does not re-organise the data into rows.</p>
  <p>I experience this with my data. We actually have much worse performance in some areas if we just run the ALTER COLUMN. However, if I run an UPDATE Table SET Column = Column for all of these fields we then get an extremely huge performance increase.</p>
  <p>The problem I have is that the database consists of hundreds of these columns with millions of records. A simple test (on a low performance virtual machine) had a table with a single NTEXT column containing 7 million records took 5 hours to update.</p>
  <p>Can anybody offer any suggestions as to how I can update the data in a more efficient way that minimises downtime and locks?</p>
  <p>EDIT: My backup solution is to just update the data in blocks over time, however, with our data this results in worse performance until all the records have been updated and the shorter this time is the better so I'm still looking for a quicker way to update.</p>