Converting NTEXT to NVARCHAR(MAX) in a MSSQL 2016 database

[Andy Dufresne]

I have a php web application that runs on a SQL Server 2016 database. The database has been upgraded over the years from SQL Server 2005 to SQL Server 2016. Being such an old database it still has some NTEXT columns in various tables.

To convert those NTEXT columns to NVARCHAR(MAX) I ran this query on each table:

alter table tablex alter column columnname nvarchar(max);

where the 'columnname' column in the 'tablex' tab le was a NTEXT column.

That seems to have worked fine on all the tables. Then I came across the following two articles, which seem to say the same thing:

https://www.sqlservergeeks.com/sql-server-why-is-ntext-so-bad-to-give-a-pain/
http://geekswithblogs.net/johnsPerfBlog/archive/2008/04/16/ntext-vs-nvarcharmax-in-sql-2005.aspx

They recommend running this query

update tablex set columnname = columnname;

after each of the abve alter table queries:

alter table tablex alter column columnname nvarchar(max);

Now those articles are quite old - and were referring to older versions of SQL Server. Do they still apply to SQL Server 2016? Is it still recommended that I run the above 'update tablex' statement after I run the 'alter table' statement?

[Erland Sommarskog]

Andy Dufresne (and...@webmail.co.za) writes:
> That seems to have worked fine on all the tables. Then I came across the
> following two articles, which seem to say the same thing:
>
> https://www.sqlservergeeks.com/sql-server-why-is-ntext-so-bad-to-give-a-p
> ain/
> http://geekswithblogs.net/johnsPerfBlog/archive/2008/04/16/ntext-vs-nvarc
> harmax-in-sql-2005.aspx
>
> They recommend running this query
>
> update tablex set columnname = columnname;
>
> after each of the abve alter table queries:
>
> alter table tablex alter column columnname nvarchar(max);
>
> Now those articles are quite old - and were referring to older versions
> of SQL Server. Do they still apply to SQL Server 2016? Is it still
> recommended that I run the above 'update tablex' statement after I run
> the 'alter table' statement?
>

Nothing has changed in this matter.

Whether you should do it or not, well that depends. By default ntext is
always stored out-of-row, whereas the MAX types are stored in-rowm when
they are below 8000 bytes in size and fits on the page.

If your data is typically always over 8K in size, it does not matter. But
if most rows have data in the 1K range, you reduce the amount of LOB pages
you need. Then again, if this means that many of the other columns spread
out over a lot more pages, so scans that does not require the MAX column
will be slower.

So there is on straight answer to this one. But altering the column to be
MAX is always a good thing, since there are so many restrictions with the
old types.