Migrating from ntext to NVARCHAR(MAX)

Question

I have table with the columns of type ntext.And for some peculiar reasons, client want to migrate the column type to NVARCHAR(MAX).

While doing so , what all potential threat should i be careful about?

Is there any possibility of data loss for the NVARCHAR(MAX) limitations?

Answer 1

No. You can alter your tables (Sql 2005+) cf this.

Or you can supply a view which casts the ntext columns to nvarchar(max) to make the client happy and leave your database unchanged.

Answer 2

"peculiar reason"? Only that ntext is deprecated and damn difficult to work with?

It's a lossless conversion. If you are unsure, use the SSMS GUI designer to generate scripts for you.

Answer 3

Is there any possibility of data loss for the NVARCHAR(MAX) limitations?

No. NVARCHAR(MAX) allows for twice as much text (2^31-1 vs. 2^30-1) as ntext.