5

I have table with the columns of type NTEXT. And for some peculiar reasons, the client wants 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?

6

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

Or, for your convenience:

alter table my_table alter column my_ntext_column nvarchar(max);

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

  • It would help to note that while you will not lose any data, by only specifying the datatype, it is possible to lose a NOT NULL setting and/or have the collation changed if it is not the same as the database's default collation. Please see my post "Beware! Beware of Unintended Changes When Altering Columns!" for full details and examples. – Solomon Rutzky Jan 31 '19 at 16:24
7

"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.

2

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.