SQL Server - Alter column length causes collateral damage?

Question

Besides the obvious possible data loss, when altering the size from varchar(MAX) to varchar(255), are there any side effects? Like:

• table-lock?
• index corruption?
• Automatic re-indexing?

This is the kind of activity that can be conducted at any time of day or it should be confined to a "maintenance window"?

Answer 1

In changing the varchar(max) column to varchar(n) or vice versa, SQL Server will update all rows of the table. This can temporarily increase the size of the table until rebuild cluster index or execute DBCC CLEANTABLE.