5

Due to some changing business requirements, I will be needing to split a large table in the near future to support new business requirements allowing a 1<=>M relationship from a table that was designed for a 1<=>1 relationship.

The challenge is that this table is fairly large and continuously used. Somewhere around 150 columns, and 50 million rows.

The approach I'm considering is as follows: (the table is named "Customers")

  1. Create two new tables, (Customers_1, and Customers_Many representing the new table structure.
  2. Copy all the data across, as appropriate.
  3. Create a view, selecting from Customers_1 and Customers_M that duplicates the Customers table exactly.
  4. Create a trigger to handle inserts / updates on the view to update the parent tables.
  5. Update foreign keys to point to the new tables.
  6. Use sp_rename to Swap the table and the view.
  7. Change code in subsequent iterations to point to the new tables, instead of the view.
  8. (Eventually) Drop the view, drop the old table.

Can anyone recommend any challenges with this approach, validate this approach, or offer different approaches?

5
  • 1
    What version and edition of SQL Server are you using? Commented Feb 3, 2016 at 16:24
  • Sql Server 2014 Enterprise x64 Commented Feb 3, 2016 at 16:30
  • 1
    This is the problem encountered when tables are not normalized to begin with. I don't envy your position.
    – datagod
    Commented Feb 3, 2016 at 16:30
  • It's certainly not a fun position, but it's more about a change in requirements than a denormalized table. Commented Feb 3, 2016 at 17:52
  • You will have to block all updates to Customer table after step 1 through 6. Otherwise all changes will be lost. Or you may need another step with make all 3 tables in sync (after step 6).
    – yahor
    Commented Feb 4, 2016 at 6:45

1 Answer 1

2

From my own experience of doing this your approach is almost identical to the approach that I took when I needed to do it. There are a few bits that I did extra prior to performing the split which gave me a better understanding of the potential impact this would have such as:

  • Query sys.sql_modules for any objects that reference the table in their definition
  • Create a scaled-down copy of the end-design in test and point clients to it (this enables you/app developers to know what is likely to need changing in the future)
  • Run a profiler trace to capture queries that are executed against the database which are not stored in the database itself.
  • Design indexes for the new table structure based on the queries identified from the steps above.

The only major difference between your approach and the one I took is I only created a single new table (I altered the original by dropping columns I did not need and then renaming it) so that it reduced the time to perform the release (I needed to copy less data doing it this way). I took a full backup prior to the release and I wasn't willing to have two copies of the same data in my database.

2
  • 2
    I'd suggest using Extended Events over Profiler since it's deprecated, especially if it's going to be in production. Commented Feb 3, 2016 at 17:57
  • 1
    @JohnM fair point, I was just mentioning what I did at the time but it's good to note going forward :) Commented Feb 3, 2016 at 18:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.