Database Reference
In-Depth Information
Listing 7-10. Check constaints: Two tables joined without CHECK constraints created
select *
from dbo.PositiveNumbers e join dbo.NegativeNumbers o on
e.PositiveNumber = o.NegativeNumber
Figure 7-7. Execution plan without CHECK constraints
As you see, SQL Server scans and joins both tables. That makes sense. Even if we had named our tables in a very
specific way, nothing would prevent us from inserting positive values into the NegativeNumbers table and vice versa.
Now let's add the check constraints that enforce the rules. You can see the ALTER TABLE statements in Listing 7-11.
Listing 7-11. Check constaints: Adding check constraints to the table
alter table dbo.PositiveNumbers
add constraint CHK_IsNumberPositive
check (PositiveNumber > 0);
alter table dbo.NegativeNumbers
add constraint CHK_IsNumberNegative
check (NegativeNumber < 0);
If you run the select again, you will see the different execution plan, as shown in Figure 7-8 .
Figure 7-8. Execution plan with CHECK constraints
SQL Server evaluated the check constraints, determined that they are mutually exclusive, and removed any
unnecessary joins.
one very important situation where you must define the check constraints is in the case of partitioned views.
Check constraints prevent access to unnecessary tables and greatly improve the performance of queries. We will discuss
partitioning views in greater detail in Chapter 15, “Data partitioning.”
Note
 
 
Search WWH ::




Custom Search