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!

Crocein

The application being written in Delphi (any version) is immaterial here, as is the ODBC Express components. It only matters that you're accessing the table via ODBC, which is fairly standardized. (IOW, the Delphi tags are not really relevant at all.) – Wit

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.

Outstanding

My concern with this is I do not want to lose any of the original data (since there is a lot of it). Will alter table automatically convert the integers to their string representations? Thanks again! – Crocein

@EvanZimmerman: ALTER TABLE .. ALTER COLUMN ... will preserve your data - as long as it can be converted to the new format. Going from INT to VARCHAR is not a problem, as long as the VARCHAR is long enough for the INT. Since INT has a max value of 2 billion, it has at most 10 digits - so a VARCHAR(20) is ample enough to hold any INT you throw at it. This will just work - no issues to be excpected! But ALWAYS test this on a copy of your actual database first!! Don't just do this in production! – Moneywort

Out of curiosity I tested what would happen if you make the text column too short. I had expected SQL Server to report an error (as happens when making a column with null values NOT NULL). However, I was a little disappointed to learn that it simply changes the value to '*'. – Bonnard

@CraigYoung that will also happen on a cast; I'm not sure if there is a setting for that. – Outstanding

@Moneywort it may be silly, but I usually cast ints as varchar(11) since that will handle negative two billion. – Outstanding

Great! Thanks everyone for the info. The new IDs I am seeing are pretty close to 20 characters wide, so for my purposes it will be changed to a varchar(50) to be on the safe side, which from your comments it seems to not be a problem :) – Crocein

Answer 2

ALTER TABLE your_table MODIFY your_column_name varchar(255) null;

Admeasure