Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > microsoft.public.sqlserver.programming > #31345

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

Show all headers | View raw

Andy Dufresne (andyza@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. 

Back to microsoft.public.sqlserver.programming | Previous | Next — Previous in thread | Find similar

Thread

Converting NTEXT to NVARCHAR(MAX) in a MSSQL 2016 database Andy Dufresne <andyza@webmail.co.za> - 2019-01-10 09:00 -0800
  Re: Converting NTEXT to NVARCHAR(MAX) in a MSSQL 2016 database Erland Sommarskog <esquel@sommarskog.se> - 2019-01-10 23:03 +0100

csiph-web