Jeff Moden (3/20/2015)

---

Thanks for the question, Dwain. It's real simple.

Assuming that you already have a hierarchy table (We'll call it "H" for this example and all names will be greatly simplified. Substitute as your heart desires) that you want to update...

1. Rename the hierarchy table from "H" to "H1".

2. Create a synonym called "H" and point it at "H1". The "H" synonym takes the place of the table in all "user" code and procs without any changes.

3. Create another hierarchy table called "H2" even if it's empty.

4. Create another synonym called "HW" (hierarchy work) and point it at "H2".

At this point in time, we have synonym "H" pointing at the currently "online" data in table "H1".

At this point in time, we have synonym "HW" pointing at the currently "offline" table "H2".

The time comes for a rebuild of the hierarchy. Here are the steps for that. The final result is that the hierarchical data was unavailable for only milliseconds even though the rebuild may have taken a minute.

1. Run the code to rebuild the "H2" table through the "HW synonym. Except for the table truncate, the prevents the need for all other dynamic SQL for the rebuild.

2. Once "H2" has been successfully rebuilt, simply repoint the "H" synonym at "H2", which contains the new current data to bring it all "online". Total downtime is whatever it takes to drop and rebuilt just the "H' synonym.

3. Repoint the "HW" synonym to the now old data in "H1". You can either keep "H1" for "previous value" comparisons, or truncate it.

4. Next update, you simply rebuild "H1" through the "HW" synonym, repoint the "H" synonym to the "H1" to bring the updated data online, and repoint the "HW" synonym to "H2" in preparation for the next rebuild.