How can I change the datatype of a column from integer to text in SQL Server?

Question

I need to change a database column from integer to string/text but I am not sure how to go about it.

This column is meant to store identification numbers, but recently the ID format changed and now the IDs contain ASCII characters as well (so with this change the new IDs cannot be stored as integers).

The application I am updating is written in Delphi 7 and uses the odbcexpress components for the SQL Server library.

Is it possible to use ALTER TABLE for this? Or does the data need to be copied to a new column as string, delete the old column, and rename the column to the old name?

Can you provide an example on how I might do this? I am not very familiar with the workings of SQL Server.

Thanks!

Answer 1

ALTER TABLE is precisely what you want to do.

Your SQL might look something like this:

ALTER TABLE dbo.MyTable ALTER COLUMN MyColumn VARCHAR(20) NOT NULL;

Note that if you have columns that reference this one, you will have to update those as well, generally by dropping the foreign key constraints temporarily, making your changes, then recreating your foreign key constraints.

Don't forget to change anything that is dependent or downstream as well, such as any variables in stored procedures or your Delphi code.

Additional info related to comments (thanks, all):
This alter column operation will preserve data as it will be implicitly casted to the new type. An int casts to varchar without a problem so long as your varchar is wide enough to accommodate the largest converted value at least. For total safety with ints, I often use a varchar(11) or larger in order to handle the widest int value: negative two billion.