Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 Change data type of populated column?

Author  Topic 

jun0
Starting Member

32 Posts

Posted - 2012-12-14 : 09:03:21
Hi, is it possible to change the data type of a column of a table which contains data (quite a lot of data).

If this is possible, what is the best way to do it. Its pretty urgent and I'm looking for the quickest and simplest way.

Thank you!

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-12-14 : 09:59:01
Yes it is possible. However, how easy it is and how resource intensive it is depends on factors such as what the current data type is and what it is going to be, whether the column has identity property, whether it is used as a foreign key in other tables etc.

Can you post what the data type is now, what you want to change it to, and how many rows you have in the table?
Go to Top of Page

jun0
Starting Member

32 Posts

Posted - 2012-12-15 : 11:42:27
The datatype now is 'varchar', and I need to change it to 'int'.
I'm sorry, I can't check the number of rows in the table right now, but i'm pretty sure its in the millions.

Is this bad news?
Go to Top of Page

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-12-15 : 12:00:22
It is not necessarily bad news. However, if there is any value that cannot be converted to an INT, then you will run into problems. I don't have a sense for how resource intensive this would be - it probably depends on a lot of factors such as the capabilities of your system, how busy the table is etc.

You can find out how many rows the table has using the following query. This is fast, you can run it safely
SELECT 
row_count
FROM
sys.dm_db_partition_stats
WHERE
OBJECT_NAME(OBJECT_ID) = 'YourTableName'
You can run the following query to find if there are any non-numeric characters in the column using the following query
SELECT * FROM YourTable WHERE YourVarcharCol LIKE '%[^0-9-]%'
That does not necessarily mean that the data can be converted to integer, it is only partially effective.
Go to Top of Page

jun0
Starting Member

32 Posts

Posted - 2013-01-03 : 09:28:03
apparently it is possible to change the data type of a populated column with the ALTER TABLE statment, is this true?

How would this be done? I have now made sure the only data in the column is numeric, so I'm ready to convert!!
Go to Top of Page

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2013-01-03 : 12:34:46
ALTER TABLE yourTable
ALTER COLUMN yourVarchar int

I've done this before on a table with 100,000,000+ rows and 100 columns (not my design!) and it took several hours. I think it would have been wiser to make a copy of the table, truncate the original table, change the data type, and then insert in batches (a few million at a time) from the copy table back in to the original.

Jim

Everyday I learn something that somebody else already knew
Go to Top of Page
   

- Advertisement -