vmssanthosh (11/15/2010)
I didnt say index is a replacement for foreign key. I says that query performance depends on the indexes not on referential integrity components. even it helps in generating a good execution plan, we always have the option of changing the execution plan using the query hints.So why we need to sacrifice the insert/update operations performance.
When we define our transactions tables with more foreign keys/constraints, it in turn reduce the insert/update operation performance.
Most of the transactions details will be retrieved using the index columns. then it will be joined with other references to get appropriate information. So retrieving data from transactions table will always be faster and it needs less/no optimization plans.
Regards
VMSSanthosh
But, query hints aren't magic. You can't control every aspect of the optimizer. You can't tell it to ignore joins that it just doesn't need with any hint, but the optimizer can ignore joins because of referential integrity. We are talking two radically different things here, and I think you're missing it. There are performance improvements offered by referential integrity. That's not saying it's magic either, but you can't just dismiss it out of hand. You need to understand what you're losing if you get rid of it. That's not even talking about the potential for dirty data that most businesses would prefer to avoid.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning