How to alter column from varchar(max) to varbinary(max) -

I was working on a project today and I had to convert one of a columns to varbinary from varchar. Well I thought I will get away with one alter statement but I end upw with the error as “Implicit conversion from data type varchar(max) to varbinary(max) is not allowed. Use the CONVERT function to run this query.” And I had to find another way. Sure I could simple add another varbinary column and convert and assign it to that but I don’t want to change the existing column order so I had to find another way to convert the current column.

Let us create a simple table to demonstrate how we could convert

--================================================--
--create table--
--================================================--
if object_id('dbo.table1') is not null
	drop table table1;
create table dbo.table1(
	col1 int not null identity(1,1),
	col2 varchar(max) 
) ;
go

next let us add few values and null to represent a real life column values

--insert test values --
insert into dbo.table1 (col2) values('test1'),(null),('test3');

--check values --
select * from dbo.table1;

Implicit Conversion Not Supported Alter Column Step0
Now that we have our table and test values in the table let us try to convert col2 varchar(max) to varbinary(max);

--alter varchar(max) to varbinary(max)--
alter table dbo.table1 alter column col2 varbinary(max);

Okay that is not a legal conversion or it require explicit conversion. If you follow the link in the reference section and check the conversion we can’t convert from varchar to varbinary implicitly. But if you check other data types (such as decimal, numeric) implicit conversion is possible from varbinary.
That being said let add a temporary column that will hold our current col2 value

--add a temporary column to hold the current value--
alter table dbo.table1 add tempCol varchar(max);

Move our col2 values to the new temp column

--transfer values from col2 to tempCol and set col2 to null--
update dbo.table1 set tempCol = col2;
update dbo.table1 set col2 = null;
go

Implicit Conversion Not Supported Alter Column Step2

--check values --
select * from dbo.table1;

Implicit Conversion Not Supported Alter Column Step1-1
Now that we moved all our values to the temp column and col2 is null let us convert it to decimal

--alter varchar(max) to decimal(1,0)--
alter table table1 alter column col2 decimal(1,0);

since it is possible to convert from decimal to varbinary let us do so

--alter decimal(1,0) to varbinary(max)--
alter table table1 alter column col2 varbinary(max);

Implicit Conversion Not Supported Alter Column Step3
now let us convert temCol values to varbinary and update col2 values

--convert tempCol to varbinary and insert it back to col2--
update dbo.table1 set col2 = convert(varbinary(max),tempCol);
go
--check values before we drop tempCol--
select * from dbo.table1;

Implicit Conversion Not Supported Alter Column Step4
Drop our temp column

--drop tempCol--
alter table dbo.table1 drop column tempCol;
--check values --
select * from dbo.table1;

Finally we have our original table with col2 altered to varbinary