To enable database tables to handle the extended character set languages I was looking to convert fields from type text to ntext.
Aware of the pending loss of support for the field types text and ntext I also chose to convert these fields to nvarchar(max).
SQL server 2016 is removing support for field type text and ntext.
In this example table I had a field called body which was of type text.
The field was to retain its name but to be changed to a type which would support a greater international language.
To prepare for SQL server 2016 I chose to also change it to type nvarchar(max).
Given below is the SQL applied to the table portfolio.
alter table dbo.Portfolio add body2 text go update dbo.Portfolio set body2 = body go alter table dbo.Portfolio drop column body go alter table dbo.Portfolio add body nvarchar(max) go update dbo.Portfolio set body = body2 go alter table dbo.Portfolio drop column body2 go
As can be seen above I begin by adding a second field of type text, called body2.
The field body is then copied to this be field body2.
The old field can now be dropped. You may prefer to check at this point that the data has been copied.
Now to create the new version of the body, giving a type of nvarchar(max).
Once more the data is copied. This time back to the body field.
Perhaps another check of the data copy?
And finally deletion of the temporary field.
