Any Benefit to Defining Foreign Keys in SQL Server without enforcing foreign key constraint in SQL Server

[Jeff Smith]

Is there any benefit to defining the Foreign Key on the fact table in SQL Server with the Foreign Key contrastraint turned off? Does it help SQL Server run queries faster?

The database is a Star Schema with a minimum of sno flaking.

Is there a benefit to defining the foreign key on a dimension table?

[ngalemmo]

Not enforcing constraints can significantly reduce the time required to load data (UPDATE/INSERT), not query it.

An enforced foreign key constraint requires the database to lookup against the referenced table every time a row is inserted or FK updated in the referencing table.  Even with caching this can consume to a considerable amount of time.

Constraint enforcement does not occur during queries (SELECT), however having FK constraints declared can sometimes help the optimizer in creating an appropriate query plan.  Most (probably all) BI tools also leverage FK declarations when reverse engineering the data model into its metadata repository to determine relationships between tables.

[BoxesAndLines]

It is also beneficial for the modeling tools. Without a defined FK constraint (enabled or disabled), the modeling tools bring in a list of tables with no relationships. It then takes hours/days to reconnect all of the tables manually based on the size of your data warehouse.

And to ngalemmo's point, I have seen performance improvements when defining PK's as well as FK's.

[Jeff Smith]

OK so define the foreign keys without the foreign key constraint.

[Sponsored content]