SQL Server refactor a column from varchar(50) to uniqueidentifier and surrounding issues

Question

I have a table message in a database that has almost a million rows. It has a externalId column which is varchar(50). The values stored in it are guid but I want to update this column to be uniqueidentifier.

So I think I am going to add a new column which is uniqueidentifier. Copy all the values across to this column and then drop the original column. Then I will rename this column to externalId.

My problem is there are hundreds of stored procs etc and I need to make sure that I don't break anything. I also have to grep through all the code and make changes so that we expect a Guid and not a string. (I am using C#)

Does anyone have some tips or recommendations?

Would I be better do just duplicate this column and not touch the existing column and make any code that does a select on it use the guid column instead of the string (currently it sometimes times-out!). I would also have to update any code then that inserts into this table to also insert a guid...)

I love legacy crap .......................

Answer 1

You could simply

alter table message
    alter column externalId uniqueidentifier

The risk is that if any of the values stored in the column are not guids, you'll see an error like:

Conversion failed when converting from a character string to uniqueidentifier.