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

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开发者_StackOverflow社区 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 .......................

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.

I would probably approach this like so:

• work on a copy
• add a new column externalIdGuid of type uniqueidentifier
• try to convert all externalId into the new externalIdGuid

If that works, then all your externalId are valid GUID's - in that case you could just simply convert the column to type uniqueidentifier:

ALTER TABLE dbo.Message
  ALTER COLUMN externalID uniqueidentifier

The only real hint I can give you is to do it all at once. Don't do this piece meal as you will have problems.

Add the column, copy the values, drop the old column, and rename the new one. Then recompile all of your stored procedures. This will give you a list of problem areas. Fix them all and recompile. If that part looks good, then move on to the code. Being legacy you'll probably find a number of other issues here that you didn't even know about.

The code is going to be the toughest area. Most likely the problems are going to involve runtime errors. Make a test plan that covers absolutely everything and go through it.

I wouldn't do this at all. Yes it would have been better if it had been a Uniqueidentifier to begin with but unless you have a specific problem that can't be overcome without changing it what's the value that you're users are getting.

If you do decide to do it. You can query the sys.procedures. Its better than using syscomments or INFORMATION_SCHEMA.ROUTINES

Well turns out after I put a Non Clustered index on that column its pretty fast. I just selected the query in Sql Server management studio and rught clicked and went 'Analyze Query in Database Engine Tuning Advisor'. It told me that this column needs an index and even generates the SQL script you need to add it :)

CREATE NONCLUSTERED INDEX [IX_message_external_id] ON [dbo].[message] 
(
    [external_id] ASC
)
INCLUDE ( [message_id],
[message_type_id],
[message_status_id],
[template_id],
[user_id],
[raw_message_body]) WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]

One very quick and safe way to check if you break anything on your dev system is to rename your table the message_legacy and make a view called message which casts externalid to uniqueidentifier. That shouldn't mess with the underlying data but will give you a workable interface to see how your stored procs and other code will behave. Remember to grant the same permissions to this view as you have on the table or you might get errors which are permission related instead of type related, which is what you really want to test.

If you get an acceptable result, then go ahead and change the column definition. Personally, I'd rename the table, create a new table with the column type changed, insert into new_table select * from old_table, then drop the old table.

Good luck!