Change column from VARCHAR(MAX) to VARBINARY(MAX)

Question

I have a table which has a VARCHAR(MAX) column, and I need to change it to VARBINARY(MAX).

I tried using the command

ALTER TABLE TableName ALTER COLUMN ColumnName VARBINARY(MAX)

but I got the error

Msg 257, Level 16, State 3, Line 1
Implicit conversion from data type varchar(max) to varbinary(max) is not allowed.
Use the CONVERT function to run this query.

The table has no data, so I can't understand why it's complaining about data conversion.

Answer 1

You cannot perform this conversion using an ALTER TABLE statement since converting from varchar(max) to varbinary(max) requires an explicit conversion. So you should follow these steps to alter your table:

1. Alter table with new column of VARBINARY(MAX)
2. If you have existing data in the VARCHAR(MAX) column, use update statement to add the data to the VARBINARY column
3. Alter table to drop the VARCHAR(MAX) column
4. Rename varbinary column to varchar name (per comment from @Ben Thul)

Answer 2

Convert Varchar to Int and then change Int to Binary.