Microsoft KB Archive/825021

= BUG: A Delete Operation That Involves a Cascade Delete Updates the Statistics Incorrectly =

PSS ID Number: 825021

Article Last Modified on 8/21/2003

-

The information in this article applies to:


 * Microsoft SQL Server 2000 (all editions)

-



SQL Server 8.0:356923



SYMPTOMS
When you delete a row in a parent table that has a column referenced by a foreign key in a child table, with cascading reference, the delete operation may not update the distribution statistics correctly. The incorrect update of the distribution statistics may result in the SQL Server Query optimizer incorrectly estimating the cost of the delete query while generating the query execution plan.



WORKAROUND
To work around this problem, use triggers to manually implement cascading referential actions for the delete instead of using the ON DELETE CASCADE clause while creating a foreign key constraint on a child table. To create triggers to work around this problem, follow these steps:
 * 1) Drop the foreign key constraint that has the ON DELETE CASCADE clause from the child table.
 * 2) Create a new foreign key constraint on the child table to reference the parent table column, without using the ON DELETE CASCADE clause.
 * 3) If the parent table and the child table do not have any other foreign key constraints, the cascading delete action can be implemented by creating a FOR DELETE trigger on the parent table. The FOR DELETE trigger should delete the rows in the child table that reference the deleted row in the parent table.
 * 4) If the parent table or the child tables have a foreign key constraint, the cascading delete action can be implemented by creating an INSTEAD OF DELETE trigger on the parent table. The INSTEAD OF DELETE trigger should delete the rows in the child tables that reference the deleted row in the parent table. Additionally, the INSTEAD OF DELETE trigger should also delete the rows on the parent table that caused the trigger to invoke, because row deletion does not occur when there is a INSTEAD OF DELETE trigger on a table.

Here is an example that illustrates the workaround. This example uses a table named parent_table for the parent table, and a table named child_table for the child table. These tables are mentioned in the &quot;Steps to Reproduce the Behavior&quot; heading that is in the &quot;More Information&quot; section of this article:   Run the following SQL statement to drop the constraint that has the ON DELETE CASCADE clause on the child_table table: ALTER TABLE child_table DROP CONSTRAINT FK_fkcol   Run the following SQL statements to create a foreign-key constraint, without the cascading reference on delete: ALTER TABLE child_table WITH NOCHECK ADD CONSTRAINT FK_fkcol FOREIGN KEY(fkcol) REFERENCES parent_table(pkcol)   If the parent_table and the child_table do not have any other foreign key constraints, then run the following SQL statements to create a FOR DELETE trigger, to delete the rows from the child_table when a row in the parent_table is deleted: CREATE TRIGGER CascadeDeleteTrigger ON parent_table FOR DELETE AS BEGIN DELETE child_table FROM child_table, deleted WHERE child_table.fkcol = deleted.pkcol END

Note If child_table has a foreign key constraint to parent_table, the constraint is evaluated before the trigger, causing the delete statement to be aborted.   If parent_table and child_table have a foreign key constraint, run the following SQL statements to create an INSTEAD OF DELETE trigger, to delete the rows from child_table when a row in parent_table is deleted:

CREATE TRIGGER CascadeDeleteTrigger ON parent_table INSTEAD OF DELETE AS BEGIN SET NOCOUNT ON

DELETE child_table FROM child_table, deleted WHERE child_table.fkcol = deleted.pkcol

SET NOCOUNT OFF

DELETE parent_table FROM parent_table, deleted WHERE parent_table.pkcol = deleted.pkcol END 



Steps to Reproduce the Behavior
  Run the following SQL statements in SQL Server Query Analyzer, to create two tables in the tempdb database, and to create a parent-child relationship between the tables with a cascading reference and populate the tables with sample data: -- Use tempdb database.

USE tempdb

-- Create parent_table with a primary key column.

CREATE TABLE parent_table (   pkcol int PRIMARY KEY )

-- Create child_table with a column that references a column in parent_table with cascading delete.

CREATE TABLE child_table (   fkcol int,    CONSTRAINT FK_fkcol FOREIGN KEY(fkcol) REFERENCES parent_table(pkcol) ON DELETE CASCADE )

-- Declare and initialize local variables.

DECLARE @limit int, @ctr int SET @limit = 1000 SET @ctr = 1

-- Populate parent_table and child_table with sample data.

WHILE (@ctr <= @limit) BEGIN INSERT INTO parent_table VALUES (@ctr) INSERT INTO child_table VALUES (@ctr) SET @ctr = @ctr + 1 END </li>  Run the following SQL statements to update statistics for the tables created in step 1: -- Clear the procedure cache, and force a recompile of the query.

DBCC FREEPROCCACHE

-- Update the distribution statistics of  parent_table and child_table.

UPDATE STATISTICS parent_table WITH FULLSCAN UPDATE STATISTICS child_table WITH FULLSCAN </li>  Run the following SQL statements to run a SELECT query on the tables, and to view the estimation plan: -- Set the query plan on.

SET SHOWPLAN_ALL ON GO

SELECT * FROM parent_table P, child_table C    WHERE   P.pkcol = 1 AND P.pkcol = C.fkcol GO

Note You may note from the EstimateRows and the EstimateExecutions columns in the result set that one row has been estimated for the join. You may also notice that the query plan uses a nested loops join. </li>  Run the following SQL statements to run a DELETE query on the tables, and to view the estimation plan: DELETE FROM parent_table WHERE pkcol = 1 GO

-- Set the query plan off

SET SHOWPLAN_ALL OFF GO

Note You may see the following information in the result set:

<ul> The query plan shows an incorrect estimate of 100 rows in the join, instead of one row.</li> The join has spool table with one row.</li></ul> </li></ol>

<div class="references_section">