Search code examples
sqlsql-serverdatabase-designsqldatatypes

Want to change table definition but amount of data is large

This is the continual question from this previous question I've asked: Change Database Column Design in SQL Server

The table I'm trying to alter the table definition is quite large (4137631 rows) so Management Studio says the table would be inaccessible during the process of converting and it could take some time to finish.

So I need advices on how to handle the situation.

  • Should I wait for the after office hour to make changes?
  • Any ideas how long would it take?
  • Or is there any other faster way to change the table definition (varchar to nvarchar)

Thanks in advance.

Solution

    • You should schedule a job until the database is not being used at all. Failing that, take the database offline first.

    • Noone will be able to tell you precisely.

    • ALTER TABLE is your only option, unless you feel like copying the entire table first (in which case you'll run into problems with referential integrity ANYWAY, not recommended).