Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
waveform
Yak Posting Veteran
93 Posts |
Posted - 2014-07-16 : 12:24:55
|
In the following example, I have a "Person" table, and a many-to-many "Relationship" table which stores the r/ship between any two people:[Person] [Relshp]---------------- ---------------------| PersonId PK|<-.-. | RelshpId PK || PersonName | \ '-| PersonId |---------------- '--| RelatedToPersonId | | RelationshipType | --------------------- There are 2 FK constraints, linking1. [Relshp].[PersonId] to primary key [Person].[PersonId], and2. [Relshp].[RelatedToPersonId] to primary key [Person].[PersonId].What kind of index structure would best support those FK constraints?Would it be:a) One combined index:CREATE INDEX IX_Relshp ON Relshp (PersonId, RelatedToPersonId)orb) Two indexes: CREATE INDEX IX_RelshpP ON Relshp (PersonId) CREATE INDEX IX_RelshpR ON Relshp (RelatedToPersonId)orc) Two "mirrored" combined indexes: CREATE INDEX IX_RelshpP ON Relshp (PersonId, RelatedToPersonId) CREATE INDEX IX_RelshpR ON Relshp (RelatedToPersonId, PersonId)Or something else?Many thanks! |
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-07-16 : 12:36:37
|
It depends on the queries that will be run, specifically the join conditions. Likely the answer is B. In fact, that's what I start out with for indexes (PKs, FKs and any unique constraints). I later may drop some "FK" indexes if I see that they are not used.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
waveform
Yak Posting Veteran
93 Posts |
Posted - 2014-07-16 : 12:50:37
|
Thanks Tara,I was a little confused because I saw conflicting info on other sites about it.Many recommend just a) as a unique index.Are there any pros/cons or use-case differences between using approach a) and b)? |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-07-16 : 13:19:50
|
If you go with A, then any queries joining on (or other things where indexes are needed) the RelatedToPersonId without PersonId will not be able to use that index. Column order is extremely important. If you are going to use both columns always, then yes A is good. But if you need them separately, then B is what you want.It depends on your queries. Can't answer it without the queries.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
|
|
|
|