Database Reference
In-Depth Information
Note that the
SELECT
statement fetches the value of the
StateProvinceID
column from the parent table
(
Person.Address
). If the nature of the data requires that for every product (identified by
StateProvinceId
) in the
child table (
Person.StateProvince
) the parent table (
Person.Address
) contains a corresponding product, then you
can rewrite the preceding
SELECT
statement as follows:
SELECT a.AddressID,
a.StateProvinceID
FROM Person.Address AS a
JOIN Person.StateProvince AS sp
ON a.StateProvinceID = sp.StateProvinceID
WHERE a.AddressID = 27234;
Both
SELECT
statements should return the same result set. Even the optimizer generates the same execution plan
for both the
SELECT
statements, as shown in Figure
18-18
.
Figure 18-18.
Execution plan when DRI is not defined between the two tables
To understand how declarative referential integrity can affect query performance, replace the
FOREIGN KEY
dropped earlier.
ALTER TABLE [Person].[Address]
WITH CHECK ADD CONSTRAINT [FK_Address_StateProvince_StateProvinceID]
FOREIGN KEY ([StateProvinceID])
REFERENCES [Person].[StateProvince] ([StateProvinceID]);
■
Note
there is now referential integrity between the tables.
Figure
18-19
shows the resultant execution plans for the two
SELECT
statements.
