Databases Reference
In-Depth Information
This check constraint makes sure that the number of vacation hours is a number
between -40 and 240, so if I request:
SELECT
*
FROM
HumanResources
.
Employee
WHERE
VacationHours
>
80
Listing 5-27.
… SQL Server will use a Clustered Index Scan operator, as shown in Figure 5-7.
Figure 5-7:
Plan without contradiction detection.
However, if I request all of the employees with more than 300 vacation hours then,
because of this check constraint, the Query Optimizer must immediately know that no
records qualify for predicate. Run the query in Listing 5-28.
SELECT
*
FROM
HumanResources
.
Employee
WHERE
VacationHours
>
300
Listing 5-28.
As expected, the query will return no records, but this time it will show the execution
plan shown in Figure 5-8.
