Presents your 
SQL SERVER E-NEWSLETTER for January 23, 2003

<------------------------------------------->

LEARN THE BEST OPTIONS FOR EFFICIENT DELETION

Sometimes you may find that delete commands for small parent tables are
executing for a long time. This may be because the optimizer is scanning
the child table of the table being deleted. SQL Server is checking all
children table values before the delete is taking place.

If you are doing ad hoc queries in deleting particular values from a
small parent table, and you know that the values in the children table 
do not correspond to the values in the parent table being deleted, 
consider disabling the foreign key constraint before deleting.

After deleting the rows from the parent table, you would enable the
foreign key constraint. When enabling the foreign key constraint, SQL 
Server will validate the values between the parent and child tables.

Another option is to add an index on the child table for the column that
has the foreign key constraint. When adding the index, the optimizer
will use the index to check the foreign key values. The optimizer will 
do an index seek rather than a table scan, causing the query to speed up
considerably.

The only reason not to create an index on the foreign key constraint is
if there are no queries that are joining the parent child tables or
affecting values in the foreign key constraint.

The sample script demonstrates deleting a value from a parent table that
has a foreign key constraint to a child table. The script creates two
tables that have a foreign key constraint between them.

Values are inserted in both tables and the A SHOWPLAN_TEXT command is
run against the parent table for a delete operation of a value that does
not exist in the child table. This showplan will show that the query is
doing a table scan.

The second showplan, after the foreign key constraint is disabled, will
show that the plan is not checking the child table for the delete operations.

The third showplan, after an index is created on the column that has the
foreign key constraint, will show that the plan is doing an index seek
for the delete operation.

-- MAKE SURE DATABASE HAS ENOUGH SPACE
SET NOCOUNT ON
GO
CREATE TABLE T1(PK1 INT IDENTITY NOT NULL CONSTRAINT PK_T1 PRIMARY KEY
NONCLUSTERED, COL1 INT NULL)
GO

CREATE TABLE T2(PK1 INT IDENTITY NOT NULL CONSTRAINT PK_T2 PRIMARY KEY
NONCLUSTERED,
          T1_PK1 INT NOT NULL CONSTRAINT FK_T2_T1 FOREIGN KEY 
REFERENCES
T1(PK1),
          COL1 INT NULL)
GO

INSERT T1 (COL1) VALUES ( 1 )
GO
INSERT T1 (COL1) VALUES ( 2 )
GO

INSERT T2 (T1_PK1, COL1 ) VALUES ( 1 , 2343)
GO
INSERT T2 (T1_PK1, COL1 ) VALUES ( 1 , 9343)
GO
INSERT T2 (T1_PK1, COL1 ) VALUES ( 1 , 8373)
GO
INSERT T2 (T1_PK1, COL1 ) VALUES ( 1 , 64)
GO
INSERT T2 (T1_PK1, COL1 ) VALUES ( 1 , 2047)
GO

SET SHOWPLAN_TEXT ON
GO
DELETE T1 WHERE PK1 = 2
GO
SET SHOWPLAN_TEXT OFF
GO
ALTER TABLE T2 NOCHECK CONSTRAINT FK_T2_T1
GO
SET SHOWPLAN_TEXT ON
GO
DELETE T1 WHERE PK1 = 2
GO
SET SHOWPLAN_TEXT OFF
GO
ALTER TABLE T2 CHECK CONSTRAINT FK_T2_T1
GO

CREATE NONCLUSTERED INDEX idx1_T2 ON T2 (T1_PK1)
GO

SET SHOWPLAN_TEXT ON
GO
DELETE T1 WHERE PK1 = 2
GO
SET SHOWPLAN_TEXT OFF
GO

DELETE T1 WHERE PK1 = 2
GO

DROP TABLE T2,T1
GO

----------------------------------------