INF: Identifying Cascading Referential Integrity from SQL Server Profiler (279033)

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]))