Changing a Column's Data Type

I have a table that contains more than 10,000 rows, and I want to change one column from a numeric data type to a float data type. How can I accomplish the change with the least impact on data availability?

You have three options for changing the column's data type. One, you can use a SELECT statement such as

SELECT a,b,c,CAST(d AS float)  AS N'd',e,f,g INTO dbo.newtable

You then use sp_rename to rename the old table, rename newtable as the old table, and reset your permissions and indexes. A second option is to use the ALTER TABLE command to change the column's data type, using the following syntax for ALTER TABLE:

ALTER TABLE { [ ALTER COLUMN   {  [ (  [ ,  ] ) ]

Or, as a third option, you can use bulk copy program (bcp) to copy out the data, then drop the table, create the new table, use bcp to copy the data into the new table, and reset permissions and indexes.

Typically, I'd recommend using the ALTER TABLE option, especially if you have only a few thousand rows of data, because this solution doesn't affect permissions. However, using the ALTER TABLE command does impact data availability, so you probably don't want to use it during regular work hours or if you have millions of rows in your table.

To learn more, visit "Cool New SQL Server 2012 Metadata" and "Report Builder and Tabular Model Databases."

—Patrick Conlan
Platform Program Manager,
Microsoft Project Team