How to split a wide table in Production? 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&lt;=&gt;M relationship from a table that was designed for a 1&lt;=&gt;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")


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


Can anyone recommend any challenges with this approach, validate this approach, or offer different approaches?
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.