In the next few days I want to describe a way to modify the definition of very large tables while keeping the table available for other queries. Some table changes are already online operations. But offline changes need extra work to keep the table available. I explore one way to do that in this series.

This article used to be a single blog post, but I broke it into four parts because it seemed too long. Read Part 1, and understand the strategy I’m using. Treat Parts 2-4 as appendixes. When you want to use this plan (or one like it), come back and use them as examples. You won’t be able to make use of the examples by cutting and pasting directly (unless you happen to be running an bike equipment store called Adventureworks), but modifications shouldn’t be too hard.

This is part 3 of the series (you can consider this Appendix 2, The main article is found here).

What you find below is exactly the same script as you find in Part 2: Implementation Example with the exception that I’ve included error handling and wrappers to handle the following goals:

If a statement fails, no data is lost

If a statement fails, other queries are not impacted (i.e. we’re online)

If a statement fails anywhere, it can be retried from the beginning with no lost work. This means for example that when data is copied to the staging table in batches, and that process is interrupted and restarted, the script “picks up where it left off”.

The process is re-runnable. This means that if the script succeeds and for some reason the script is run again then no errors are raised and no work is performed.

The last step, the switch, is an atomic transaction. It succeeds 100% or fails and rolls back 100%.

I present without any further comment, the scripts (with error-handling).

One question/suggestion, have you considered using a new schema (e.g. Sales_New) and creating the new table in that schema? The benefit is that all the defaults, keys, triggers, and indexes can retain the same names as the original, and the switch becomes a single ALTER SCHEMA…TRANSFER operation. (or two, one for the old table and one for the new)

Excellent comment Rob! In fact I did think of using a new schema. But I thought of it too late and the article was already published. It’s definitely the simpler way to go (and simpler is better).
Maybe I’ll add an extra part onto this series explaining that strategy.