Change column type from ntext to varbinary(max)

Question

I have a table that has ntext field. MSDN says that ntext is deprecated and they suggest other data types:

ntext, text, and image data types will be removed in a future version of Microsoft SQL Server. Avoid using these data types in new development work, and plan to modify applications that currently use them. Use nvarchar(max), varchar(max), and varbinary(max) instead.

In my particular case it was decided to switch to varbinary(max). I tried to alter the table definition but that didn't work.

ALTER TABLE MyTable ALTER COLUMN MyColumn VARBINARY(MAX);

What are the possibilities to change the type to varbinary(max)? I tried change the type from ntext -> nvarchar(max) and then from nvarchar(max) -> varbinary(max) but that is not possible (error: Implicit conversion from data type nvarchar(max) to varbinary(max) is not allowed).

The only working solution is to add a new column of type varbinary(max), convert the existing value to the new column and then drop the old column. This takes WAY TOO MUCH time (on my dataset of about 15GB it takes about 30 minutes). That's why I am investigating other possibilities to achieve the same (possibly in-place = without moving data and conversion).

Answer 1

I presume you went with varbinary(max) because your ntext column had non textual data in it? In that case, I think you're going to have to add a separate varbinary(max) column to your table, then run a conversion operation to copy from the ntext to the new column. Then, delete the old column, and rename the new column to the old name.

"Implicit conversion from data type nvarchar(max) to varbinary(max) is not allowed" means that you're going to have to be explicit about the conversion.