Changing varchar(max) to varchar(255) requires rebuilding table

Question

Our developers (prior to me being DBA) set the data type for a field to VARCHAR(MAX) when in reality it only needs to be VARCHAR(255). This wasn't discovered until after our table had grown to over several million records and almost a terabyte of data. When I go to design mode on that table to test this change in our test environment I find that the script it creates makes a _tmp table copy of the table and then drops the original and renames _tmp to the original. I seem to remember learning the reason behind this had something to do with having to move the data from the "LOB" section of a record to the fixed length section of a record (Or so my memory recalls). However, I can't find any documentation on why exactly this occurs (instead of just changing the size in place). Can someone point me in the right direction so I can better explain to management why making this change now on a several million record table is going to take a long time.

Answer 1

The script that SSMS generates is not the best one.

You should be able to use simple ALTER TABLE:

ALTER TABLE TableName
ALTER COLUMN ColumnName VARCHAR(255) NOT NULL

Obviously, make sure in advance that the text that you store in this column would fit into 255 characters limit.

I'd strongly recommend to try it in test environment first to get an idea how long it would take. I'm not sure, and it may depend on the version and edition of your server, but it is likely that this kind of change would be performed not as a metadata change, which means that for a terabyte of data it would take a significant amount of time.

---

This question (Why does ALTER COLUMN to NOT NULL cause massive log file growth?) has a lot of details about ALTER COLUMN.