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

15
votes

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.

2
sql-server
if you have the data in the table then you cannot change datatype like that.because existing data will not allow you to do that. - Kiran1016
As I said, the table has no data. - Doug
sorry,missed the last line - Kiran1016
If you have no data, you can drop the column and create it with its new type. - flup
stackoverflow.com/questions/14305224/… - bummi

2 Answers

22
votes

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)
0
votes

Convert Varchar to Int and then change Int to Binary.