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

It seems like this conversion is going to have to happen at some point. If you search, you'll find many people going from text to varchar(max) and stating it takes 20+ minutes to convert. My two cents after researching for a few minutes, so don't take it as gospel.

If your table just takes inserts, you could convert the existing data in a holding table and then rename the tables so the holding is then production. Then move any newly created data from the old table during your down time.

Handling updates makes things more complex of course.

Answer 2

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.

Answer 3

Adding the extra column is probably the best way to go. I favour doing this kind of thing in steps to reduce risks

1. Add the column varbinary(max) as nullable
2. Modify your insert code to populate both columns
3. At your leisure, say overnight, run the UPDATE statement with a CAST
4. Remove all code support for the old column, ensure new column is read
5. Drop the old column, and change the new column to be non null if needed