SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


FKs with indexes...


FKs with indexes...

Author
Message
PiMané
PiMané
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1424 Visits: 1347
Hi,

I've read several articles stating that FKs should have indexes on both tables (primary and referenced) and it has quite logic since when deleting/updating on the referenced table the primary one will be searched for those records...
I also read some stating that it's best to have few wider indexes rather than lot narrow one's due to index maintenance, size on disk, what happens when a record is updated/deleted, ....
But it seems like these both statements are a bit contradictory since creating indexes on FKs will create lots of narrow indexes... for example I have a table that will have 30 "extra" narrow indexes, and since they are FKs no wide index will cover the narrow ones.
Is it always advisable to create indexes on FKs or, since in this case, the records aren't that much deleted and the keys never changed, can we "skip" the FKs indexes on the primary tables?

Thanks,
Pedro



If you need to work better, try working less...
GSquared
GSquared
SSC-Dedicated
SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)

Group: General Forum Members
Points: 30219 Visits: 9730
It depends.

There are always cases where "the rules" need to be broken.

In a case like that, with 30+ FKs needed, that gets into the realms of both (a) you probably won't benefit much from 30+ indexes, and (b) what is going on in that database?!? :-)

But really, and seriously, you'd have to look at query patterns for the table and the related tables. Some might benefit from indexes. Maybe even all of them. But it will really depend on the queries that are in use.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
PiMané
PiMané
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1424 Visits: 1347
It's a products table... it has VAT, warranty, supplier, units, .... and many other references.
The referenced tables don't have many records (top on has 20) but the products can have 16000 or many many more...
And since the referenced tables can't be updated (code column is read only after insert) it can only be deleted what doesn't happen that much...
Referenced tables with few records usually means that it isn't inserted/updated/deleted that regularly (in our case the tables are configured usually once and left alone) so narrow indexes on the primary table for these ones can be a rule to break... I guest..
We also have some other cases that probably require the primary table index...
In cases were the FK columns can't be updated cause they are read-only and the data isn't deleted (just a bit column with record state) the indexes don't make sense right?

Thanks,
Pedro



If you need to work better, try working less...
GSquared
GSquared
SSC-Dedicated
SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)

Group: General Forum Members
Points: 30219 Visits: 9730
I'd have to run some tests to say definitively whether indexes would help or not, but I can say I've seen similar systems and indexes weren't needed there.

So, probably you're fine without.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
PiMané
PiMané
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1424 Visits: 1347
Thanks,

Also if the indexes are necessary they'll probably show up on the missing index DMV query.... :-)

Pedro



If you need to work better, try working less...
GSquared
GSquared
SSC-Dedicated
SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)

Group: General Forum Members
Points: 30219 Visits: 9730
That's definitely possible.

Or they'll show up in index plans when you're reviewing performance issues.

Until it turns into something that needs fixing ...

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
PiMané
PiMané
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1424 Visits: 1347
Thanks,
Better wait and see what shows on the sys.dm_db_missing_index_groups and/or the sys.dm_exec_query_plan

Pedro



If you need to work better, try working less...
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search