I am struggling to find the best way to migrate some "varchar" columns to "nvarchar". One of the options i am using is to add new nvarchar column(s) then update the values from the original column, drop the original column and rename the new one to the old name.
I know it will generate a lot of UNDO and REDO data. Still, I have other limitations (mostly by SQL Server not supporting parallel DDLs and multi-column ALTER table operations), so let's focus on how to run the update statement faster.
My Oracle experience is telling me to use internal parallelism, but is it available in SQL Server?
I am not able to run this statement in parallel, although I especially created the table to be a heap table (no clustered index).
update t set new_col_1 = col_1 ,new_col_2 = col_2 ... , new_col_N = col_N ;
There are 3 text columns holding 400GB of data. There is limited IO performance from AWS RDS (10000 IOPS). We have just 4 hours of downtime window.
In this particular migration the online rebuild is not an option as the data must be migrated (to nvarchar) before the application can be started. During startup it is checking whether the actual data types correspond to the defined ones (in the application metadata repository).
I am aware of the fragmentation, but we just have no choice. Still, if there is some ONLINE rebuild command it might be useful as we will be able to migrate and de-fragment later. Actually, as one of the preparations steps, we are dropping the clustered index. Later this index will be created again, which i believe will fix the fragmentation issue, as we will move from a heap to b*tree structure.
It is very frustrating that we can not use any other "parallel" technique. I am thinking to try manual parallel update, by running a few parallel update statements against not overlapping ranges of the target table. Still, the lock escalation, could be the next issue, as i am going to update millions of records in each of those updates, and most probably the SQL server will try to escalate to table lock, which will lock other updates, and a dead lock will be the end result..