Here are two examples of using foreign key constraints to improve performance. The first one works correctly, but the second (because it adds NOCHECK) returns incorrect results.
Listing 1 contains code that creates two tables, populates each with a value of 1, and adds a foreign key constraint between the tables. Listing 2 shows a query that determines whether any rows in t1 match any rows in t2. If you run Listings 1 and 2, the query returns the expected result—one row matches. However, if you run a graphical showplan from the Query menu in Query Analyzer and examine the results (not shown in this article), you see that the query never checked t1. SQL Server did only a table scan of t1. So how can SQL Server return a correct existence check? Simple: the foreign key constraint tells SQL Server that a t1 row must exist to match each t2 row. The example data set is small, but you can imagine the substantial processing savings in similar queries on larger data sets if SQL Server can eliminate a table from the join.
I recently learned that using NOCHECK when you create foreign key constraints can wreck this performance trick by returning incorrect results. Listing 3 shows code that creates two tables and populates t1 with a value of 1 and t2 with a value of 2. The code includes a foreign key constraint with NOCHECK for t2. Run Listing 3, then run Listing 2 again; you'd expect the query to return no matching rows because a value of 1 doesn't equal a value of 2. Bad news: SQL Server says that the t2 row matches the t1 row.
If you again run a graphical showplan, you'll find that, as in the first example, SQL Server doesn't access t1. SQL Server assumes that the foreign key constraint is enforced and that the tables contain matching rows. This is an optimizer bug: A foreign key constraint you create with NOCHECK isn't enforced, so SQL Server returns an incorrect result set.
Either avoid using NOCHECK in foreign key constraint definitions or check your code carefully. Thanks to SQL Server MVP Itzik Ben-Gan for pointing out this problem in the private MVP forums and for providing the sample script that I based this example on. Microsoft says that SQL Server 2000 Service Pack 4 (SP4) will fix this bug.
