Tuning the Schema - Professional SQL Server 2005 Performance Tuning - page 207

Figure 8-11
Now look at the query plan (shown in Figure 8-12) for populating the table Worker with the following
query, which doesn't include referential integrity:
DECLARE @I int
DECLARE @BossID INT
SET @BossID = 1
SET @I = 1
WHILE @I < = 1000
BEGIN
IF @I < = 100
BEGIN
SET @BossID = @I
END
ELSE
BEGIN
SET @BossID = @I%100
IF @BossID = 0
SET @BossID = 100
END
INSERT INTO dbo.Worker VALUES (@I, 'Adam', @BossID)
SET @I = @I + 1
END
Now, with the statement, ALTER TABLE dbo.Worker ADD CONSTRAINT FK_Worker_Boss FOREIGN KEY
(BossID) REFERENCES dbo.Boss (BossID) , let's add the foreign key constraint on the Worker table
for the BossID column and re-evaluate the query plan.
As you can see in Figure 8-13, the second plan has added a clustered index seek operation. This is neces-
sary to provide the integrity that's defined by the constraint.
However, this overhead is generally deemed an acceptable price to pay to ensure the data quality.
Additionally, defining these integrity mechanisms at the database level is better than attempting to
define it in the front-end applications. The reason is that there may be several different applications
that can operate against a common table. Coding the integrity checks in all those applications
is impractical.