Topic: MERGE vs Triggered VIEW
Kind time of days, colleagues.
Select @@ version: Microsoft SQL Server 2014 - 12.0.2402.0 (X64) Aug 13 2014 11:36:34 AM Copyright (c) Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1)
There was a question of productivity for creativity authors for SSIS ETL a packet.
There is everyday Bulk a loading in tables from csv. Deltas are not present, . the data each time all. (Percent of changes we assume <=10 %.)
Further accordingly there is a comparing (about 6 million) and (new to add, existing to update, removals are not present therefore the table about 80 million).
It is implemented as follows in a packet:
Lookup tasl and if on a condition (a unique dial-up 3 attributes) coincide - there is an insertion attempt in "fake" View (with INSTEAD OF Insert trigger').
The trigger does Update in the necessary table Target.
CREATE TRIGGER dbo.trigger_name ON dbo. View_name INSTEAD OF INSERT AS BEGIN SET NOCOUNT ON; UPDATE t SET t.col1 = tt.col1; t.col2 = tt.col2; t.col3 = tt.col3; t.col4 = tt.col4; t.col5 = tt.col5 ....... FROM dbo.target_table t INNER JOINinserted tt ON t.predicateCol1 = tt.predicateCol1 AND t.predicateCol2 = tt.predicateCol2 AND t.predicateCol3 = tt.predicateCol3 END
The example "fake" View on which hangs the trigger:
CREATE VIEW dbo. View_name AS SELECT TOP (1) * FROM dbo.target_table
I insist on change of architecture in favor of classical MERGE statement + Partitioning. But belief from an underside (without demonstrative and it is feeble ) simply heap. Speak 100 times checked up - MERGE worse.
Whether in this connection at me arose : real e pluses in favor of architecture (Lookup +View + trigger instead of insert at update of the data) for productivity improving?
In advance thanks for the argumentation and speculation on a subject.