MS SQL Server: a faster way to update table structure

On my MS SQL Server, I needed to change the datatype of a field. Specifically, it was going from VARCHAR(40) to VARCHAR(50).

I tried to use the GUI.

Upon making the change, I received a notification that one of the indexes on the table was going to be deleted. I made note of the index so that I could re-create it after.

This table has 10+million rows of data. I also got an alert indicating the update will take awhile to complete.

Upon trying to save my changes, MS SQL Server Studio went into a state of “Not Responding”. Eventually it timed-out and the change was not saved. 😠.

I found this interesting: looking at the Activity Log, behind-the-scenes it had created a temporary table but then rolled-back this change.

The solution was to not use the MS SQL Server Studio GUI but rather write SQL:

ALTER TABLE myName
ALTER COLUMN myColumn myDataType

This took less than 1 second to execute, and there weren’t any errors!

If I did that to start with, it would’ve saved me the wait-time on dropping/re-creating the index, and the eventual time-out of the actual table modification.