Best way to update table schema for huge tables (SQL Server)

Question

I have a few huge tables on a production SQL 2005 DB that need a schema update. This is mostly an addition of columns with default values, and some column type change that require some simple transformation. The whole thing can be done with a simple "SELECT INTO" where the target is a table with the new schema.

Our tests so far show that even this simple operation, done entirely inside the server (Not fetching or pushing any data), could take hours if not days, on a table with many millions of rows.

Is there a better update strategy for such tables?

edit 1: We are still experimenting with no definitive conclusion. What happens if one of my transformations to a new table, involve merging every five lines to one. There is some code that has to run on every transformation. The best performance we could get on this got us at a speed that will take at least a few days to convert a 30M rows table

Will using SQLCLR in this case (doing the transformation with code running inside the server) give me a major speed boost?

Answer 1

Are you applying indexes immediately, or in a secondary step? Should go much faster without indexing during the build.

Answer 2

Have you tried using alter table rather than moving data to a new table? Whyever would you use Select into? Just alter your current structure.

Answer 3

We have a similar problem and I've found that the fastest way to do it is to export the data to delimited files (in chunks - depending on the size of the rows - in our case, each file had 500,000 rows), doing any transforms during the export, drop and recreate the table with the new schema, and then do a bcp import from the files.

A 30 million row table took a couple of hours using that method, where an alter table took over 30 hours.

Answer 4

Add the column allowing null, then do the update to the default value manually, then re-alter the table to add the default value. This way you can control the updates and do them in smaller chunks.

Answer 5

I have a similar sounding problem which occurs reasonably frequently.

Our database caches the results of a remote stored procedure which, occasionally, expands with new fields.

This table is millions of rows (and now up to about 80 fields) with a couple of indices and having played around with #temp tables and such (even using bcp to temporary files); I use the select into a new table option:

• create a new table with the new structure
• do a select into that table
• drop the original one
• rename the new table to the old one's name