March 7, 2016 at 10:23 pm
Is it a good practice to index foreign keys in all tables. I'm working on a design phase of a db.
Should a non-clustered index with just the foreign key column good enough?
Please provide your suggestions.
Many thanks.
March 8, 2016 at 1:17 am
Sometimes it's enough, but it depends on the shape of your workload.
It could be a good starting point, but you may have to include more columns if your workload indicates so. Keep an eye on missing indexes and expensive queries DMVs and go back to your indexes to include more columns where needed.
-- Gianluca Sartori
March 8, 2016 at 5:45 am
There is absolutely no hard and fast rule "Do this and you'll be fine." Picking and choosing indexes is extremely dependent on the workload in question. Foreign keys are good candidates for indexes because they are referenced so frequently in queries and, depending on the queries, act as filtering mechanisms. That doesn't say that all foreign keys should get indexes. It also doesn't say whether or not you should add additional columns to the index using the INCLUDE operator. It also doesn't say whether or not the queries are filtering often enough with enough effectiveness to justify the added overhead of maintaining the index through INSERT/DELETE/UPDATE operations. Further, it doesn't say whether, in this instance, the foreign key is used so frequently to retrieve the data, that maybe it should be the clustered index rather than the primary key.
In short, yeah, foreign keys are a good candidate for indexing, but without a complete understanding of the workload, I wouldn't suggest just blindly adding indexes to them.
"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
March 8, 2016 at 7:12 am
Thanks Grant!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply