T-SQL Tuesday #106

This is a response to T-SQL Tuesday #106, Trigger Headaches or Happiness, by Steve Jones.

I can only recall one time in the past several years (at least a decade) that I’ve found triggers to be useful. It involves data migration.

The problem: You have a massive, high-activity table. Let’s call this Table_A. You need to make significant changes to it. For example, the clustered index needs to change. How do you accomplish this?

The solution: Create a script that does the following:

  1. Create a second table, which we’ll call Table_B. This one will eventually become the new Table_A, so design it with your changes in mind.
  2. Create a third table, which we’ll call Table_C. This one is like Table_A as it is now, except that it includes an additional identity column, and is clustered on that column. Assuming there’s an existing clustered index on Table_A, recreate that as a non-clustered index on Table_C. Depending on how Table_A is updated, you may need additional columns to track what updates occur.
  3. Create a trigger on Table_A. This trigger duplicates all changes in Table_A to Table_C.
  4. Looping via a suitable batch size for your environment, write all rows from Table_A to Table_B.
  5. Looping again, write all rows from Table_C to Table_B (taking into account the appropriate insert/update/delete logic for your situation). Note where you stopped with Table_C, the “high water mark” for that identity column.
  6. Call sp_rename to change Table_A to Table_D, then again to change Table_B to Table_A.
  7. From the high water mark, write from Table_C to the newly-renamed Table_A.
  8. My favorite part: Drop Table_C, Table_D, and the trigger.

There are caveats to this method, of course, but they have been acceptable for my situations. They are:

  • The table is unavailable (non-existent, really) between the two renames in Step 6. This is an extremely brief window, but it does need to occur. Also, in order to apply a trigger, the table needs to be briefly locked, which may present a problem.
  • Step 7 is present so that changes between Steps 5 and 6 are carried over to the new table. However, these can occur after the new table is active following Step 6, meaning that the following scenario is possible:
    • A row is updated in Table_A, and the change is carried over to Table_C.
    • The renames occur.
    • The same row is updated in the new Table_A.
    • Th second change is overwritten with the first change.

If the table you wish to migrate has a considerable number of updates and deletes, then this solution may present a data integrity problem. However, for insert-heavy tables, it has proven to work very well. Also, a second trigger on the new table and some additional logic could circumvent the second issue I described, depending on your situation.

So there you go – a use for triggers.

Godspeed!

One thought on “T-SQL Tuesday #106”

Leave a Reply

Your email address will not be published. Required fields are marked *