gotchasqlModerate
Why does performing a delete on my partitioned view result in a Clustered Index Insert?
Viewed 0 times
performingwhyresultclusteredinsertdeleteviewdoesindexpartitioned
Problem
I have a partitioned view that has the below insert trigger (poor mans partition).
When I perform a DELETE, I get the below query plan:
Trigger on the view:
.... etc
When I perform a DELETE, I get the below query plan:
delete from factproductprice where pricedate = '20170725'Trigger on the view:
ALTER TRIGGER [dbo].[factProductPriceDelete] ON [dbo].[FactProductPrice]
INSTEAD OF DELETE AS
BEGIN
IF @@ROWCOUNT = 0 RETURN;
DECLARE @PriceDate DATE
SELECT @PriceDate = CAST(PriceDate AS DATE) FROM DELETED
IF @PriceDate BETWEEN '20140101' AND '20141231'
BEGIN
DELETE FROM dbo.FactProductPrice2014
WHERE ProductId IN (SELECT ProductId FROM DELETED) AND SalesPriceSchemeId IN (SELECT SalesPriceSchemeId FROM DELETED) AND PriceDate IN (SELECT PriceDate FROM DELETED)
END
IF @PriceDate BETWEEN '20150101' AND '20151231'
BEGIN
DELETE FROM dbo.FactProductPrice2015
WHERE ProductId IN (SELECT ProductId FROM DELETED) AND SalesPriceSchemeId IN (SELECT SalesPriceSchemeId FROM DELETED) AND PriceDate IN (SELECT PriceDate FROM DELETED)
END
IF @PriceDate BETWEEN '20160101' AND '20161231'.... etc
Solution
INSTEAD OF triggers completely replace the triggering action.The inserted and deleted pseudo-tables represent changes that would have been made, had the triggering statement actually executed. Row-versioning cannot be used for these triggers because no modifications have yet occurred, by definition.
SQL Server modifies the execution plan for the triggering DML statement when an
INSTEAD OF trigger exists. Rather than modifying the affected tables directly, the execution plan writes information about the changes to a hidden worktable.This worktable contains all the data needed to perform the original changes, the type of modification to perform on each row (delete or insert), as well as any information needed in the trigger for an
OUTPUT clause.The Insert in your execution plan represents writing to this hidden worktable. When you capture a post-execution plan for the statement, you will see this hidden worktable being used as the deleted and inserted pseudo-tables.
See my SQLPerformance.com article, Interesting Things About INSTEAD OF Triggers.
Context
StackExchange Database Administrators Q#182154, answer score: 11
Revisions (0)
No revisions yet.