Query Optimizer and FK constraints

I have read that if you add a FK using WITH NOCHECK, the query optimizer will not use this FK in any query plan it generates. That got me to thinking about how and why would the query optimizer even consider a FK when generating a query plan? What benefit i开发者_运维问答s there to the query engine if a FK is in place?

---

If the foreign key is "trusted", SQL Server can use the fact that the columns targeted by the constraint are in a unique index on the other table. If you have a join with the table the FK is targeting and you only use the columns present in the key, it can remove the join.

For example, let's say you have two tables: Order and Product, and the Order table has a ProductId that is a foreign key to Product.Id. Then, the optimizer know that this query:

SELECT Order.Id, Product.Id
FROM Order
LEFT OUTER JOIN Product ON Product.Id = Order.ProductId

... is the same as this query:

SELECT Order.Id, Order.ProductId
FROM Order

.. and the extra join to Product can be removed. It can do that since it knows there will be at most one row in Product for each row in Order, and the values will be the same, so removing the join will not affect the query.

If Order.ProductId is NOT NULL, then it would be able to do the same for an INNER JOIN. If it's nullable, it can't since it doesn't know that there will be a row in Product, so the join could cause Order rows to be dropped; it's not the same.

However, if you add Product.Name to the SELECT clause, then it can't remove the join in either case.

Yeah, I know: it doesn't seem very useful. In practice, I haven't really seen too many cases where this actually makes a difference. Usually it's only a factor for dynamically generated SQL or code generation where the joins are fixed and only the SELECT clause changes.

You can read about this here:

http://explainextended.com/2009/10/15/constraints-and-the-optimizer-in-sql-server-foreign-key/

As for why WITH NOCHECK makes a difference, when that is specified the key is not "trusted", which means that perhaps not all values in the column satisfy the constraint. WITH NOCHECK only enforces the constraint on new data; any old data might violate the assumption, so SQL Server plays it safe and keeps the join there.

---

What may happen is that if you have a query that join other foreign key table, and if there is a foreign key constraint, the query engine can take advantage of using the foreign table index.

You can test it out by looking at the query execution plan in SQL Server Management Studio. It is under Query->Include Actual Execution Plan. Try one with FK and one WITH NOCHECK and see the difference.