SQL Server Column Conversion

I have a table, call it course, with a column called courseCode.

At the moment the column type is tinyInt, but I need to convert this to a varchar since it now needs to store 'A'. Before anyone jumps on me for poor design, I did not design this table...haha.

Anyway at the moment, the table stores only the digits 0 through开发者_运维技巧 9.

Is this as simple as the following statement?

ALTER TABLE dbo.Course
ALTER COLUMN courseCode VARCHAR(30)

Or will this cause unwanted conversion issues? I have never had to do much like this and would love to hear some best practices on converting column types when your clients have data stored with the old type. Thanks.

Check whether there are constraints or defaults attached to that column. (sub your actual table for mytable)

sp_help mytable

Check for defaults, checks, foreign keys, indexes, unique, primary key - anything

If there are any attached to the column, you will have to drop them and recreate after the conversion.

The actual operation of

ALTER TABLE dbo.Course
ALTER COLUMN courseCode VARCHAR(30)

Is 100% correct, for tinyint at least. Almost everything can be converted to varchar* in SQL Server, as long as the size fits.

I'm also a big fan of sizing columns correctly, so if you need to expand from 0-9 to 1-char alpha, just use Char(1).

_{* There are some exceptions like GUID, which need to go through varchar(max), then cut back down to size.}

That should work fine for you with that particular datatype conversion. Others could give you a headache, but tinyint to varchar's pretty straight-up...

I think that's enough...as long as some constraints linked to that field don't block you. The best way to find out is to make o copy of your db and try on the copy.