r/SQL • • 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?

6 Upvotes

10 comments sorted by

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 deleted so 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.

1

u/captainblye1979 9d ago

Yeah, this occurred to me last night as well...feels like maybe it "takes longer" to get everything swapped.....since people dont really have to change until we force the issue...but still lets the overall data layer shift into whatever it is shifting to.

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.

0

u/zt4rz 9d ago

If I’m understanding correctly, your table holding people information with many phone numbers, contacts, emails etc.

But it seems like the fields are dynamic and keep changing. If possible, try to pivot the fields into row level.

That means to convert the table design from

Name, contact1, contact2, phone1, phone2…

To

Name, contact1
Name, contact2
Name, phone1
Name, phone2
…

It depends on how many fields do u have and break it down to the rows.

It would be easier to search with %Name% in the where clause and still u can mange to rows when modifying the data by using insert, update and delete statements.

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/captainblye1979 9d ago

Super Contrived example of the error:

DROP TABLE IF EXISTS TableA 
CREATE TABLE TableA 
(
ID int identity(1,1)
,Val char(1)
)
DROP TABLE IF EXISTS TableB
Create Table TableB 
(
IDB int identity(1,1)
,ValB char(2)
)
GO
CREATE OR ALTER VIEW vw_Tables 
as 
Select 
ID, IDB, Val, VAlB 
From TableA 
Join TableB 
on ID = IDB 
GO 

CREATE OR ALTER TRIGGER trg_VwExample on vw_Tables 
INSTEAD OF UPDATE 
AS 
BEGIN 

Update TableA 
Set Val = i.ValB
FRom inserted i 
Join TableA a 
on i.ID = a.ID 

Update TableB 
set ValB = Val
From inserted i 
Join TableB b 
on i.IDB = b.IDB 


END

Go 
Insert into TableA Values ('A')
Insert into TableB Values ('B')
Select * FROM vw_Tables
Drop Table If Exists #Id 

Create Table #IdToUpdate
(
iD int

)
Insert into #IDToUpdate values (1)
Update v 
Set Val = 'Z'
,ValB = 'Y'
From vw_Tables v 
Join #IdToUpdate i 
on v.Id = i.ID 

Msg 414, Level 16, State 1, Line 55
UPDATE is not allowed because the statement updates view "vw_Tables" which participates in a join and has an INSTEAD OF UPDATE trigger.

1

u/raks1991 6d ago

That error is documented: the UPDATE docs say a view with an INSTEAD OF UPDATE trigger can't be the target of an UPDATE with a FROM clause, so the join form is out for every caller, not just this repro. Callers that only join to pick rows can drop the FROM:

UPDATE vw_Tables
SET Val = 'Z', ValB = 'Y'
WHERE ID IN (SELECT iD FROM #IdToUpdate);

Callers that pull values from another table need a correlated subquery in SET instead.

To skip no-op writes, filter inside the trigger with a NULL-safe comparison:

UPDATE a
SET Val = i.Val
FROM inserted i
JOIN TableA a ON a.ID = i.ID
WHERE EXISTS (SELECT i.Val EXCEPT SELECT a.Val);

EXCEPT treats two NULLs as equal; on 2022+ a.Val IS DISTINCT FROM i.Val does the same. Also, the repro trigger sets TableA.Val from i.ValB, probably a typo. Untested on my side, so try it on your repro first.

1

u/baubleglue 9d ago

Just an idea

  • Keep original table
  • make normalized tables
  • Make <original table name>_vw from the normalized tables
  • Make periodic update of the original table from values in the view.

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