Clever Geek Handbook

Changing the SQL Server Data Type

In SQL Server, we have a table with 80 million records and 3 column data types - floating right now. Now we need to change the float column in the Decimal column. How do you do this with minimal downtime?

We ran a normal ALTER statement to change the datatype, but the log file got full and went into an out of memory system exception. So let me kindly solve this problem better.

We cannot use this method. How To: Create 3 new temp columns and update an existing dataset and drop the existing column and rename the temp column to live columns.

+3


source to share


3 answers


I did the same in one of my projects. Below are the steps you can follow when minimum registration, minimum downtime with least complexity.

  • Create a new table with new data types with the same column names (no index if the table requires an index to be created after the data is loaded into the new table) but with a different name. For example the existing table is EMPLOYEE, then the new table name should be EMPLOYEE_1. Keep in mind all constraints such as foreign key, and anything you can create before loading or after loading will not affect anything in terms of performance. But recommend not to create, since the existing table has the names of the names that you renamed after you renamed the table.

  • Keep in mind that the maximum precision in the new data type at the maximum precision is available in your existing table.

  • Load data from an existing table into a new table using SSIS with fast load option to prevent logging from happening to the temp database.

  • Rename the old table with EMPLOYEE_2 while idle and rename EMPLOYEE_1 to EMPLOYEE.

  • Change table definition for foreign key, default or any other constraints.

  • Go live and create indexes at lower load times on the table.



using this approach, we changed the datatype of the table where we had about billions of records. Using this approach, you can have minimal downtime and log into tempDB as SSIS fastboot option will not log anything to the database.

+1


source


It's too long for a comment.

One of these approaches might work.

The easiest way is to refuse. Well almost, but do this:



  • Rename the existing column to something else (say _col

    )
  • Add a calculated column that performs the transformation.

If the data rarely changes (so you only need read access), create a new table. That is, copy the data into a new table with the correct type, then drop the original table and rename the new table to the old name.

You can do something like this by replacing the tablespaces rather than renaming the table.

0


source


Another way:

  • Add a new column to the table with the correct data type.
  • Insert the values ​​from the float column into the decimal column, but do so in x records.
  • When done, remove the float column and rename the decimal column to what was the float column name.
0


source






More articles:


All Articles