Index Foreign Key & Other Indexes

Question

Index Foreign Key & Other Indexes

Rechana Rajan

SSCertifiable

Points: 7717
More actions
January 2, 2017 at 10:26 pm

#320178

Is it a good practice to index all foreign keys?
We have Index on all foreign keys currently and for some queries I can see index seek using those indexes along with a key lookup.
So I am forced to add a new index based on that query\SP.
Do i need to keep both indexes or can i delete the index related to foreign key.
January 2, 2017 at 10:52 pm

This was removed by the editor as SPAM

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Rechana Rajan SSCertifiable Points: 7717 More actions · Answer 1

JasonClark (1/2/2017)
Yes, it's good practice to maintain indexing. As.
Performance becomes great on maintaining the relationship on a delete of a primary/unique key.
also, SQL Server can effectively find the rows to join to when tables are joined on primary/foreign key relationships.

Thanks Jason.

But then when i create addition indexes based on requirement and when an insert happens all the indexes has to be updated and its a costly affair right.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Index on foreign keys is a good place to start, but it may not be the best index to have. It's only really needed if rows in the parent table are going to be deleted, or the primary key changed.

If you've got another index on (foreign key column, other columns), then you definitely don't need another index just on (foreign key column)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Rechana Rajan SSCertifiable Points: 7717 More actions · Answer 3

GilaMonster (1/3/2017)
Index on foreign keys is a good place to start, but it may not be the best index to have. It's only really needed if rows in the parent table are going to be deleted, or the primary key changed.
If you've got another index on (foreign key column, other columns), then you definitely don't need another index just on (foreign key column)

Thanks Gail,

So the process is on going and suitable indexes are created based on demand.

Will create the indexes on foreign keys and then later remove them based on other indexes getting created.

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 4

My guidelines for FK-indexes: Always put an index on the FK columns, unless that hurts the performance of that particular system. ( i.e. your milage may vary ! )

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Rechana Rajan SSCertifiable Points: 7717 More actions · Answer 5

ALZDBA - Tuesday, January 3, 2017 6:36 AM
My guidelines for FK-indexes: Always put an index on the FK columns, unless that hurts the performance of that particular system. ( i.e. your milage may vary ! )

Thanks Johan