In SQL Server 2008, I have got a table of around 3 million records. I want to change the type of one of its columns from float to int. But seems it will take a lot of time. Is there any way to accomplish this very fast?
+1
A:
You can try Make New table and make small cursor for sending data from one table to another table in smaller parts.
If your table has date filed you can move your data by quarter of year, So you can let to breath between queries.
adopilot
2010-01-12 12:09:58
I closed sql server to cancel the time-consuming data type change process (it was taking very long). But now that I have started SSMS again, I can't browse the tables in my database and I get this error: Error 1222: Lock request time out period exceeded! Should I wait until the lock is released?
Maysam
2010-01-12 12:26:02
Probably, Sometimes SSMS do not allow browse tables when servers is on heavy load.
adopilot
2010-01-12 12:33:22
+2
A:
Drop any constraints, indexes and triggers for that table they also slow down a update.
affan
2010-01-12 12:37:57
A:
More than likely what is happening here is that SSMS is creating a temporary table with the old schema and copying all the rows to this table. It then creates a new table, with the new schema and copies back the rows from the old table. I don't think there is anything in SSMS you can set to change this, and it may be the only way to make this particular schema change.
Randy
Randy Minder
2010-01-12 13:11:54
+1
A:
Add a new int column to the table, update the new column to the value of the old column, drop the old column, rename the new column.
My gut feel is that that will be faster, but I could be wrong!
I'm assuming since it's a float just now, that it's not used in any indexes.
Doogie
2010-01-12 13:41:26
A:
ALter table is usually faster than using SSMS. HOwever inthis case, I agree with doogstar, since you have to convert all the data to int, I'd create a new column populate it (which you can do in batches to improve performance and avoid a table lock) and then drop the old column and rename the new one.
HLGEM
2010-01-12 16:32:53