Databases Reference
In-Depth Information
Figure 5-8.
Contradiction detection example.
Note that, this time, instead of a Clustered Index Scan, SQL Server is using a Constant
Scan operator. Since there is no need to access the table at all, SQL Server saves resources
like I/O, locks, memory and CPU, making the query to be executed faster.
Now, let's see what happens if I disable the check constraint:
ALTER
TABLE
HumanResources
.
Employee
NOCHECK
CONSTRAINT
CK_Employee_VacationHours
Listing 5-29.
This time, running the Listing 5-28 query once again uses a Clustered Index Scan
operator, as the Query Optimizer can no longer use the check constraint to guide its
decisions. Don't forget to enable the constraint again by running the following statement:
ALTER
TABLE
HumanResources
.
Employee
WITH
CHECK
CHECK
CONSTRAINT
CK_Employee_
VacationHours
Listing 5-30.
The second type of contradiction case is when the query itself explicitly contains a
contradiction. Take a look at the query in Listing 5-31.
SELECT
*
FROM
HumanResources
.
Employee
WHERE
ManagerID
>
10
AND
ManagerID
<
5
Listing 5-31.
