Any Benefit to Defining Foreign Keys in SQL Server without enforcing foreign key constraint in SQL Server
3 posters
Page 1 of 1
Any Benefit to Defining Foreign Keys in SQL Server without enforcing foreign key constraint in SQL Server
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?
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?
Jeff Smith- Posts : 471
Join date : 2009-02-03
Re: Any Benefit to Defining Foreign Keys in SQL Server without enforcing foreign key constraint in SQL Server
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.
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.
Re: Any Benefit to Defining Foreign Keys in SQL Server without enforcing foreign key constraint in SQL Server
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.
And to ngalemmo's point, I have seen performance improvements when defining PK's as well as FK's.
BoxesAndLines- Posts : 1212
Join date : 2009-02-03
Location : USA
Re: Any Benefit to Defining Foreign Keys in SQL Server without enforcing foreign key constraint in SQL Server
OK so define the foreign keys without the foreign key constraint.
Jeff Smith- Posts : 471
Join date : 2009-02-03
Similar topics
» Foreign Keys and the query optimizer in SQL Server 2K8
» Foreign Key Constraints
» Derived Fact table with additional measures / foreign keys ... ?
» Fact Table with huge number of Blank (or Empty) foreign keys
» Foreign key reference
» Foreign Key Constraints
» Derived Fact table with additional measures / foreign keys ... ?
» Fact Table with huge number of Blank (or Empty) foreign keys
» Foreign key reference
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum
|
|