3

I know there are a number of topics on this question, but I'm always seeking more insights.

I have a large table with a billion+ records. The amount of records could be reduced and archived, but the size will still be large. My task is to change a existing data type of a single column where the old value of data is safe to convert into the type.

Here are some of my approaches:

1 - Drop the constraints on the table that impact the targeted column, drop the indexes that also impact the targeted column, add a new column with NULL's at the end of the table, update the new column with the old column values in chunks of 10K, 50K or 100K increments, drop the old column when data has been copied and reapply indexes for that column only.

2 - Copy all data into a new table with the data type change in chunks as before, verify data is completed, drop the old table, rename the new table to the old and apply indexes.

3 - Import all data from another data source like a flat file to a new table with the data type change using BULK INSERT and MERGE SP's, basically like option 2 with having 2 duplicate tables, verify data, drop old to replace with new table and apply indexes.

What would be the fastest and safest option? Are there other options I'm not considering? I've updated 100 million records for other tables really well with option 1. The bigger the table, the harder option 1 becomes due to the time duration of updating.

3

I have always gone with Option #2, and have done so in such a way that there was minimal impact on the system as it was always assumed that that application would be running continuously, or down for 30 minutes tops. So, I would build the new table and slowly migrate the data into it over the course of a week. I would use a SQL Agent job to insert rows and an AFTER UPDATE, DELETE trigger to keep already migrated rows in sync.

I have detailed this approach in an article on SQL Server Central: Restructure 100 Million Row (or more) Tables in Seconds. SRSLY!.

  • 1
    Always option 2 for me too. – Danielle Paquette-Harvey Mar 5 '15 at 18:40
  • It's just for reporting and I can update over the course of a few days with no impact to applications. I also have a no trigger policy on the table because it's part of the ETL source table. Triggers degrade performance. – Fastidious Mar 5 '15 at 20:52
  • 1
    @Fastidious If doing over a few days then what I described in my article still works. You would just use a more aggressive set of parameters (larger batch size, less time between batches, etc). Regarding the "no trigger" policy: you can also use Change Data Capture, though I have not tried it and do not have an example of it. Regardless, if you cannot prevent DML changes while you migrate the data (unlikely if it will take a few days to migrate), then you will need to use one of those two things. Triggers here are not bad if the clustered index is good, and they will only exist for a few days. – Solomon Rutzky Mar 5 '15 at 21:31
  • 1
    Will flag as answered, but overall, reimporting data worked out quicker because of being able to filter out a lot of data not needed. – Fastidious Mar 7 '15 at 2:37
  • 1
    @Srutzky: It wasn't really a requirement so you are fine. Updating the existing data set was too long in terms of duration. Copying to another table was shorter, but still too long. BULK INSERT was a lot faster just due to BULK INSERT. All approaches were right, but one really presented the missed opportunity to filter out data that can still stay at the flat file level as Kimball's methodologies have suggested for years. I'll take it. :) – Fastidious Mar 8 '15 at 0:35

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.