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 COLUMNstatement 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?