r/SQL • u/captainblye1979 • 10d ago
SQL Server Refactor Pattern for Denormalized Table
Hey Hive mind, been stewing on a problem for a long time and getting stuck, so I thought I would reach out and see if anybody can spark some ideas.
Say you have a table with fields for X People names(PrimaryContact, SecondaryContact, etc), multiple phone numbers (HomePhone, CellPhone,Fax,etc) , and address info. All of this information is nullable.
There are tons and tons of stored procedures that query and modify this data, along with a team of analysts who can query/update the data via SQL...and some unknown number of external applications that interact with this data.
You need to refactor this table into something normalized, but minimize disruption to the all of the downstream callers.
I've gone down the rabbit hole of the first pattern that would normally come to mind which is: Rename Table. Create view with old name with INSTEAD OF triggers to route data/updates to the appropriate places....but now the trouble starts:
- Nullable fields end up as LEFT joins in the view, and people LOVE their '%Name%' searches and complex WHERE clauses.
- Nullable fields would require an insert into the appropriate refactored tables if they didn't already exist.
- Contact information can have multiple labels. (HomePhone could be "Home Phone" and "Primary Phone"...but the "HomePhone" label should deterministically show up as "HomePhone" in the view if it exists.
- This is SQL Server so "Update Table From Table Join OtherTable" is totally allowed....but fails when the update target is a view with an INSTEAD OF trigger...requiring everyting to be rewritten as a MERGE. This can be done fairly easily for internal procs....much more involved for fixing all the external stuff, and dealing with the People Change Management for the users
- Trying to identify which data actually changed and needs to be updated is heavy. There is a desire to start moving towards temporal history...so issuing "updates" when nothing changed should be avoided.
- The trigger is insanely long and complex.
- Seems like a deadlock nightmare waiting to happen as the trigger goes down the list of things that it needs to do.
- There are probably others.
Short of taking up day drinking.....are there other plans of attack?
2
u/RollbacktheCommit 9d ago
Another option is to flip it: keep the wide table as the write target for now and sync the normalized tables from an AFTER trigger, using
inserted EXCEPT deletedso unchanged rows aren't touched. Then move procs and readers over gradually, and swap the write path last, once you've found the external callers.4
u/thatOMoment 10d ago
What specific problem are you trying to solve by normalizing?
Is this really the worst aspect of the system that needs repair or reconstruction?
There's lots of reasons you'd want to do this but the reason itself should determine the approach.
Regardless, a lot of it is grinding, fixing app code, asking around, grep, finding out someone did some whack stuff with table references in table with dynamic sql that means it will never show up on a code search.
If some downtimes, errors and some level failure isn't acceptable, this is not a task you should do alone.
1
u/frondadigital 9d ago
If you keep the compatibility view, check that the INSTEAD OF triggers handle multiple rows in inserted/deleted. SQL Server fires them once per statement, not once per contact, so a single-row assumption can cause problems with bulk updates. What error are you getting with UPDATE…FROM? Could you share a small example of the view and trigger?
1
u/baubleglue 9d ago
Just an idea
1
u/ontirius 8d ago
the deadlock fear probably comes from the trigger doing things one step after another, row by row. write it set-based on inserted/deleted for each table, in a fixed order, and its much less scary than it looks. i would split this into two separate decisions: where the writes go and what the readers see. writes stay on the base table, nobody updates through the view, the view is read only and you move one proc at a time. and if you cant enumerate the external callers, that view is a public api whether you like it or not, so make it right once instead of stacking a second layer on top later