Microsoft KB Archive/279033

= INF: Identifying Cascading Referential Integrity from SQL Server Profiler =

Article ID: 279033

Article Last Modified on 10/3/2003

-

APPLIES TO


 * Microsoft SQL Server 2000 Standard Edition

-



This article was previously published under Q279033



SUMMARY
This article explains how to use SQL Server Profiler to identify the occurrence of referential cascading actions.



MORE INFORMATION
To implement cascading referential integrity, specify one or both of the following clauses when you issue a CREATE TABLE or UPDATE TABLE statement:
 * ON DELETE CASCADE
 * ON UPDATE CASCADE

If you do not want to implement cascading referential integrity, specify one or both of the following clauses when you issue a CREATE TABLE or UPDATE TABLE statement:
 * ON DELETE NO ACTION
 * ON UPDATE NO ACTION

When you do not specify a clause, the latter is the default setting.

You cannot trace cascading deletes and updates in the context of regular events such as TSQL:StmtCompleted and TSQL:BatchCompleted. However, when you execute a statement that performs cascading updates or deletes in SQL Server Profiler, you can trace these cascade actions in the execution plan.

To trace cascading deletes and updates:
 * 1) Start SQL Server Profiler.
 * 2) Click the Events tab.
 * 3) In the Available Event Classes window, click to expand Performance, and then click Execution Plan.
 * 4) Start the Profiler trace.

To start the Profiler trace:
 * 1) Start SQL Server Profiler.
 * 2) Click File, and then click New Trace.
 * 3) Connect to the SQL Server server you wish to profile with the appropriate security options.
 * 4) Click the Events tab.
 * 5) In the Available Event Classes window, click to expand Performance, click Execution Plan, and then click Add.
 * 6) Click Run to begin the trace.

The following example traces cascading actions: CREATE TABLE parent(   parentid int primary key,    parentname varchar(30),    parentdob datetime) go

CREATE TABLE child(    childid int primary key,    childname varchar(30),    childdob datetime,    parentid int constraint chd1 references parent(parentid) on delete cascade on update cascade) go

INSERT INTO parent VALUES(1, 'John Smith', '11/04/1956') INSERT INTO parent VALUES(2, 'Jane Doe', '01/16/1961') go

INSERT INTO child VALUES(1, 'Tom Smith', '05/21/1986', 1) go

set showplan_all off go delete from parent where parentid=1 go The cascade action appears as follows: Execution Tree -- Sequence |--Table Spool |   |--Clustered Index Delete(OBJECT:([pubs].[dbo].[parent].[PK__parent__571DF1D5]), WHERE:([parent].[parentid]=1)) |--Clustered Index Delete(OBJECT:([pubs].[dbo].[child].[PK__child__59063A47])) |--Merge Join(Inner Join, MERGE:([parent].[parentid])=([child].[parentid]), RESIDUAL:([child].[parentid]=[parent].parentid])) |--Table Spool |--Sort(ORDER BY:([child].[parentid] ASC)) |--Clustered Index Scan(OBJECT:([pubs].[dbo].[child].[PK__child__59063A47]))

Keywords: kbinfo KB279033

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.