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

View previous topic View next topic Go down

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

Post  Jeff Smith on Fri Jan 24, 2014 12:40 pm

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?

Jeff Smith

Posts : 471
Join date : 2009-02-03

View user profile

Back to top Go down

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

Post  ngalemmo on Fri Jan 24, 2014 1:01 pm

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

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

Post  BoxesAndLines on Fri Jan 24, 2014 3:40 pm

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.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

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

Post  Jeff Smith on Sat Jan 25, 2014 11:41 am

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

Jeff Smith

Posts : 471
Join date : 2009-02-03

View user profile

Back to top Go down

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

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum