Michael J. Swart

April 27, 2012

Modifying Tables Online – Part 5: Just One More Thing

Filed under: SQLServerPedia Syndication,Technical Articles — Tags: , , — Michael J. Swart @ 10:50 am

SERIES: Modifying Tables Online

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.

Just One More Thing

So Rob Volk commented on Part 3 of my blog. The comment prompted me to write a part five (which you’re reading now). So you can thank him for this bonus section.

I’m going to quote Rob directly. He wrote:

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)

He’s absolutely right. The switch step becomes much much simpler and simpler is almost always better (as it is in this case). My migration would start by creating some extra schemas and then creating the staging table. In the Adventureworks example I’ve been using, that looks something like this:

use AdventureWorks2012
create schema staging;
create schema obsolete;
CREATE TABLE staging.SalesOrderHeader(
-- etc...

So now our DB is prepared and looks like this:

Copy the data over to the staging table the same way. In my SalesOrderHeader example, that code doesn’t change at all except that I replace “Sales.SalesOrderHeader_new” with “Staging.SalesOrderHeader”.

When ready, the switch now includes code like this:

ALTER SCHEMA obsolete TRANSFER Sales.SalesOrderHeader;
ALTER SCHEMA Sales TRANSFER staging.SalesOrderHeader;
drop trigger obsolete.t_i_SalesOrderHeader;
drop trigger obsolete.t_u_SalesOrderHeader;
drop trigger obsolete.t_d_SalesOrderHeader;

Looks simple right! It is. Simple is better. The schema now looks something like this:

A few things to be careful of:

  • Foreign keys: Although no renaming is necessary, they still have to dropped from and to the obsolete table. And foreign keys pointing to the new table still need to be added.
  • Existing triggers: They should recreated on the new table.

That’s It

So dear reader, that’s the series. It was fun for me to explore in depth an intermediate topic. Thanks for bearing with me. I know the series was a little dry, but I figured that I wanted to use this walk-through for myself as a template for future migration projects and that if I thought it was useful, maybe you would think so too.


  1. Glad to be an inspiration! I’ve been using this technique at my current job for about a year now, to load data into staging tables and swap them in as live data. We went with this instead of table partitions to work around the Enterprise Edition requirement (although we’re now on Enterprise anyway). We also keep 3 schemas in play so we can revert to earlier versions of the data if necessary.

    Now see what you did? I may have to blog about this, even though I’ve already blogged this year!

    Thanks, and great job on this series!

    Comment by Rob Volk — April 27, 2012 @ 11:42 am

  2. Yep, you should blog as often as you shower. At least once a year (whether you need to or not)

    Comment by Michael J. Swart — April 27, 2012 @ 1:49 pm

  3. […] Part 5: Just One More Thing […]

    Pingback by Modifying Tables Online - Part 4: Testing | Michael J. Swart — April 30, 2012 @ 1:11 pm

  4. […] Part 5: Just One More Thing […]

    Pingback by Modifying Tables Online - Part 3: Example With Error Handling | Michael J. Swart — April 30, 2012 @ 1:11 pm

  5. […] Modifying Tables Online – Part 5: Just One More Thing – A bonus post concludes the final part of this excellent series from Michael J. Swart (Blog|Twitter). […]

    Pingback by Something for the Weekend - SQL Server Links 04/05/12 — May 4, 2012 @ 7:23 am

  6. Coming late to the party here, but one other thing to watch out for here (and has bitten me in the past) is that permissions are dropped on a schema transfer. So if you’ll need to re-grant (or deny) any permissions after the ALTER SCHEMA statement.

    Comment by Ben Thul — April 23, 2021 @ 1:36 pm

RSS feed for comments on this post. TrackBack URL

Leave a comment

Powered by WordPress