Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


FKs with indexes...


FKs with indexes...

Author
Message
PiMané
PiMané
Mr or Mrs. 500
Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)

Group: General Forum Members
Points: 592 Visits: 1334
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
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14385 Visits: 9729
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é
Mr or Mrs. 500
Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)

Group: General Forum Members
Points: 592 Visits: 1334
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
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14385 Visits: 9729
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é
Mr or Mrs. 500
Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)

Group: General Forum Members
Points: 592 Visits: 1334
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
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14385 Visits: 9729
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é
Mr or Mrs. 500
Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)Mr or Mrs. 500 (592 reputation)

Group: General Forum Members
Points: 592 Visits: 1334
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