Efficient method to update character length of columns in large table I have a table with 80+ million records. There are 2 columns which are currently varchar(MAX), which I would like to update to varchar(255) to save space. It is not an issue if data will be truncated. This table has 3 non-clustered indexes on it. 

SQL Server version: 2012 Web Edition

A solution that would require table locking or taking the database offline would be acceptable.

What I have tried:


ALTER COLUMN: SSMS does not let me use the ALTER COLUMN statement because there is an index that uses that column.
Updating in batches: does not work efficiently (or at all) since the unique identifier (or date field) that would be needed to keep track of which records were updated is not indexed and therefore the queries just hang. Also I read that updates would generate a lot of logs.
Adding a new table and inserting in batches: does not work because of same reason above. 
SSMS Export Wizard: Creating a new table with the correct column data types and exporting from original works within a few hours ONLY IF I don't create the indexes on the table. If I first create the indexes and then run the export, the process gets stuck at about 25/30 million. The first few million imports fairly quickly but gets slower as it goes on. I can confirm this after I had it run for 12 hours. In the former case, I am unsure how to build these indexes as I am concerned about how much memory/cpu/space it will end up taking on my server
Do work on test server and re-attach: This last point wasn't tried yet but is next on list if I don't get another idea: Transfer/copy database (via file transfer) to test server, try applying the change there without having to worry about performance costs and then re-attaching to original server. (I can also see how long and how much space an index rebuild would take, but then again that wouldn't offer me accurate information for the production environment.)


Any ideas or comments on how to accomplish this?
I would suggest the following steps


When possible, change your database recovery mode to Simple Recovery if otherwise
Drop your index(es) on the two varchar(max) columns (as you will need to rebuild it anyway)
Run the following

update MyTable Set MyCol_X=substring(MyCol_X, 1, 255), MyCol_Y=substring(MyCol_Y, 1, 255);
Alter table to change the column data type

alter table MyTable alter column MyCol_X varchar(255);

alter table MyTable alter column MyCol_Y varchar(255);
Rebuild your indexes that were dropped
Set recovery mode to previous state before step 1 is executed