question

pjkarthikmca avatar image
pjkarthikmca asked

DataType Change

HI In SQL Server, We have table with 8 crore of records and 3 column data type is float right now. Now we need to change the float data type column into Decimal column. How to proceed it with minimum downtime? We fired the usual ALTER statement to change the data type, but log file got filled and going to system out of memory exception. So kindly let me the better way to solve this issue. We cant use this technique Like: Creating 3 new temp columns and updating the existing data batch wise and dropping the existing column and renaming the temp column to live columns. Regards Karthik
indexesdatatypeschange-trackingtype
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

why can't you use the technique you described?
0 Likes 0 ·

1 Answer

·
Magnus Ahlkvist avatar image
Magnus Ahlkvist answered
If you don't have the possibility to create Three new columns, batch over the data and finally dropping old columns and renaming the new columns, I guess that's because you have a problem finding a long enough window for the job. What you could try, but I urge you to performance test the solution first: Create a new table with the correct datatypes. Rename existing table Create a view which does something like this: SELECT CAST(col1 as decimal(x,y)) as col1, CAST(col2 as decimal(x,y)) as col2, CAST(col3 as decimal(x,y)) as col3, --Any other columns you which are defined in the table FROM OriginalTableWithNewName UNION ALL SELECT col1, col2, col3, --Any other columns needed FROM NewTable Now your application can use the view. You would want to create an instead of-trigger on the view to handle inserts, updates and deletes. Inserts are simple - just use the INSERTED virtual table as source and insert into the new table with the correct datatypes. Deletes and Updates are likely to see a performance hit, when you have to use the INSERTED and DELETED virtual tables in WHERE and JOIN predicates in the instead of-trigger. When view and instead of-trigger is there, you can start moving data from the original table to the new table, some hundred thousand rows at the time. Transaction log will be filled also during this time, but you won't have to handle the whole change in one go. Either go shop for a large enough disk to handle the larger transaction log and a large enough backup storage for the transaction log backups. When all the rows have come into the new table, you can drop the old table, drop the view and rename the new table to the original table name. The technique with the view could also be used to handle adding Three new columns to the existing table and batch over rows. You'll still need a view and you'll still need an instead of-trigger, but your deletes and updates will be easier to manage in the view, since there's only one table. The performance will however still not be fantastic, as you'll need to use OR-conditions in the WHERE-clause of the instead of-trigger. The view definition would - instead of a UNION ALL - use a COALESCE or ISNULL with oldcol and newcol, to determine for each row from which column to fetch the data.
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thanks Magnus for your reply and yes, we dont have that much big window for the deploying this script. So, ill discuss about your technique with my lead and will conclude it. Thanks Kevs for your reply.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.