1

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:

  1. ALTER COLUMN: SSMS does not let me use the ALTER COLUMN statement because there is an index that uses that column.

  2. 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.

  3. Adding a new table and inserting in batches: does not work because of same reason above.

  4. 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

  5. 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?

  • 3
    You would have to rebuild any index that references the column anyway. So, drop the index, alter the column, and re-create the index – Aaron Bertrand Feb 6 '19 at 19:25
  • Also see this answer from Martin Smith - dba.stackexchange.com/a/15008/8783 – Kin Shah Feb 6 '19 at 19:29
  • What percentage of the `varchar(max) column data is longer than 255 charatcers now? An what percentage stores the data inline instead of on separate pages? Each of these will affect how long the ALTER statement will take. if the data is mostly <=255 characters, and is inline, ALTER will be faster than if the data is not already inline and is longer than 255 characters. – Laughing Vergil Feb 6 '19 at 19:34
  • @AaronBertrand I didn't take that into account. I am assuming you mean after I insert/copy new data I will have to rebuild? So it doesn't index as it inserts? – Rivka Feb 6 '19 at 19:42
  • @LaughingVergil I'm not able to run a quick query for that with the 80 million records, but I checked on a smaller live table whose data is populated same way and it seems a very very small percentage (5k/630k estimated) – Rivka Feb 6 '19 at 19:43
0

I would suggest the following steps

  1. When possible, change your database recovery mode to Simple Recovery if otherwise

  2. Drop your index(es) on the two varchar(max) columns (as you will need to rebuild it anyway)

  3. Run the following

    update MyTable Set MyCol_X=substring(MyCol_X, 1, 255), MyCol_Y=substring(MyCol_Y, 1, 255);

  4. 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);

  5. Rebuild your indexes that were dropped

  6. Set recovery mode to previous state before step 1 is executed

  • Please explain why I would "need to rebuild the indexes anyway" with method #4 outlined in my OP, with creating the index first and then using the export wizard? – Rivka Feb 8 '19 at 16:13
  • because you have to remove the indexes first before you can alter the column, so after index removed and then your work done, you of course want to re-create the indexes. So maybe my word of "rebuilding" index should be more accurate with "recreating index" :-) – jyao Feb 8 '19 at 19:51
  • My question is on the Export Wizard method, not with the alter column method. If I create a new table with the correct length, with the indexes already created, and then do an export, why do I have to rebuild the indexes after? – Rivka Feb 11 '19 at 18:57
  • I got you @Rivka, in that case, you do not have to rebuild index. However, as a best practice, when we upload data to a table (frequently seen in OLAP), we usually drop all indexes (except for PKs) on a table, and after data is loaded, we rebuild index(es), this proves much quicker than loading data with indexes on the table. – jyao Feb 11 '19 at 19:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.