Changing 8 column type of a big table from nvarchar(max) to varchar(x) with EF code Code-first

45 views Asked by At

I have a very big table(T1) with a Tera byte of data in the Prod environment. There are some columns with nvarchar(max) which are such a miss. The table is created by code-first Ef core. Since it is very time-consuming to do it with migration we want to do it by:

  1. creating a new table (T2) with new types for those columns like varchar(100) with code-first.
  2. Copying all data from T1 to T2 (DBA process) (needs downtime)
  3. Renaming those with code-first and creating migration file
  4. Exporting script of step 3 and run it directly in Database with DBAs
  5. After achieving success in step 4 update the database with the renaming migration. Since changes were done in step 4 and we have the migration record in the database table it will execute quickly.
  6. Now we can drop the T1 with migration
  7. Exporting its script and run it directly in the database
  8. Updating database with step 7 migration

So do you have such experience or idea to add?

0

There are 0 answers