Change data type of populated column? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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?

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?

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.

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!!

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources