This information was published quite a while back in the book:
Improving .NET Application Performance and Scalability
J.D. Meier, Srinath Vasireddy, Ashish Babbar, Sharon Bjeletich and Alex Mackman
I mention this because the entire chapter goes into a lot of detail on improving the overall performance of .NET applications and includes information on SQL Server performance considerations that will improve the overall application performance experience.
The information can be found in Chapter 14 - Improving SQL Server Performance
found under the topic - Define All Primary Keys and Foreign Key Relationships
Define All Primary Keys and Foreign Key Relationships
Primary keys and foreign key relationships that are correctly defined help ensure that you can write optimal queries. One common result of incorrect relationships is having to add DISTINCT clauses to eliminate redundant data from result sets.
When primary and foreign keys are defined as constraints in the database schema, the server can use that information to create optimal execution plans.
Declarative referential integrity (DRI) performs better than triggers do, and DRI is easier to maintain and troubleshoot than triggers are. DRI is checked by the server before the server performs the actual data modification request. When you use triggers, the data modification requests are inserted in the Inserted and Deleted temporary system tables, and the trigger code is run. Depending on the trigger code, the final modifications are then made or not made,
The sample screen shot in Figure 14.2 shows an execution plan that accesses only one table, although two tables are included in a join in the query. Because there is a declared foreign key relationship between the authors table and the titleauthor table, and the au_id column in the titleauthor table is not allowed to be null, the optimizer knows it does not need to access the authors table to resolve the query. The result of the SET STATISTICS IO command also shows that the authors table is never accessed.