SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 Change data type of populated column?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jun0
Starting Member

United Kingdom
32 Posts

Posted - 12/14/2012 :  09:03:21  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 12/14/2012 :  09:59:01  Show Profile  Reply with Quote
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

United Kingdom
32 Posts

Posted - 12/15/2012 :  11:42:27  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 12/15/2012 :  12:00:22  Show Profile  Reply with Quote
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

United Kingdom
32 Posts

Posted - 01/03/2013 :  09:28:03  Show Profile  Reply with Quote
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!!

Edited by - jun0 on 01/03/2013 09:33:27
Go to Top of Page

jimf
Flowing Fount of Yak Knowledge

USA
2869 Posts

Posted - 01/03/2013 :  12:34:46  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000