Databases Reference
In-Depth Information
There are two reasons for this change. First, since the
CustomerType
column is no
longer required, there are no columns requested from the
Customer
table. However,
it seems like the
Customer
table is still needed, as it is required as part of the equality
operation on a join condition. That is, SQL Server needs to make sure that a
Customer
record exists for each related record on the
Individual
table.
Actually this validation is performed by the existing foreign key constraint, so the Query
Optimizer realizes that there is no need to use the
Customer
table at all. As a test,
temporarily disable the foreign key by running the following statement:
ALTER
TABLE
Sales
.
Individual
NOCHECK
CONSTRAINT
FK_Individual_Customer_CustomerID
Listing 5-24.
Now run the Listing 5-23 query again. Without the foreign key constraint, SQL Server
has no choice but to perform the join in order to make sure that the join condition is
executed. As a result, it will use a plan joining all three tables, similar to the one shown
previously in Figure 5-5. Finally, don't forget to re-enable the foreign key by running the
statement in Listing 5-25.
ALTER
TABLE
Sales
.
Individual
WITH
CHECK
CHECK
CONSTRAINT
FK_Individual_Customer_CustomerID
Listing 5-25.
Now for an example of contradiction detection; first, I need a table with a
check constraint and, handily, the
Employee
table has the following check
constraint definition:
(
[VacationHours]
>=(-
40
)
AND
[VacationHours]
<=(
240
))
Listing 5-26.
