0

I am dealing with a problem that most of our columns were created with default EF behaviour which makes string as nvarchar(max). However that doesn't combine well with indexes.

I tried the putting the [MaxLength(100)] attribute onto the specific column and generate a migration. That generates the alter table statement that when run on a database (with a lot of data) spikes the DTU and basically trashes the DB.

I am now looking for a safe way how to proceed with this (let's say that the column name is "FileName"):

  1. Create a column FileNameV2 with [MaxLength(100)].
  2. Copy data from FileName column to FileNameV2.
  3. Delete FileName column.
  4. Rename FileNameV2 to FileName

Would this approach work or is there any better / easier way (especially one that doesn't upset EF)?

Jakub Holovsky
  • 6,543
  • 10
  • 54
  • 98
  • I find this interesting "*spikes the DTU and basically trashes the DB*" how does it trash it? – TheGeneral Jul 17 '20 at 06:54
  • I reckon the alter statement results in quite a large transaction log and not sure how this works in the background - but I saw quite a big growth of the database size when this was proceeding and after a while I had to kill it (also maxed out our database size on Azure - which might be the reason of the trashing :) however I am not 100% sure) – Jakub Holovsky Jul 17 '20 at 07:05
  • Some other discussion; https://sqlstudies.com/2016/07/14/altering-a-column-in-a-large-table-a-case-study/ – Jeremy Lakeman Jul 17 '20 at 07:19
  • Also https://stackoverflow.com/questions/10754665/change-column-types-in-a-huge-table/10754753#10754753 – Jeremy Lakeman Jul 17 '20 at 07:22

1 Answers1

0

The main issue I found out later was that our SQL Azure database had max size 2 GB so when I was doing the change and the db had 1,5 GB it then reached its size probably when doing the transition from navarchar(max) to nvarchar(100). So the learning is to double check your max size of DB on Azure just to be sure you don't hit this threshold.

Jakub Holovsky
  • 6,543
  • 10
  • 54
  • 98