Summary:

Microsoft will be removing support for the ntext data type from a future release of SQL Server. In preparation for this, new Sage CRM installs use the nvarchar(max) data type instead of ntext for multiline text fields. How does this affect upgrades? Will the data types for these fields be updated automatically?

More info:

Microsoft stated on the release of SQL Server 2005 that their intention is to remove the ntext, text and image data types in a future SQL Server version.

http://technet.microsoft.com/en-us/library/ms187993.aspx

As a general rule, the Sage CRM installer does not change column data types as part of upgrades. The reason for this is that this may cause unforseen issues with upgrades. Since CRM is updated independently of SQL Server, the installer does not change column data types.

There’s quite a difference in functionality in an ntext data type versus nvarchar (nvarchars are generally far more useful), so it’s quite possible that customisations (especially custom SQL queries) would be affected by such a change. Core CRM functionality should be fine with the change.

It’s still entirely possible to change ntext fields to nvarchar fields, but we would strongly recommend carrying out a good deal of testing before deploying changes to a live environment. The statement used is as follows:

alter table table_name alter column column_name nvarchar(max)

You’d generally need to carry out a CRM metadata refresh after changing any data type directly on the database. It is suggested that you pay special attention to the ewaresystem log after the metadata refresh, in case you run into any problems refreshing custom views.

These changes may have an impact on performance. Nvarchar(max) fields are generally stored on the table rather than on the LOB; the article referenced below has some useful information:

http://geekswithblogs.net/johnsPerfBlog/archive/2008/04/16/ntext-vs-nvarcharmax-in-sql-2005.aspx

It’s an old article (it’s for SQL Server 2005), but the basic behaviour has been confirmed to be the same on SQL Server 2012. Essentially, if you convert a column to nvarchar(max), then you may want to try updating the data to itself afterwards, as SQL Server will move it back from the LOB to the table if the column length is less than 8,000 bytes. This would deliver a substantial performance increase.