good cluster candidate on join table

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.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

good cluster candidate on join table

Author

Topic

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2010-12-16 : 17:20:13

Greetings

Let us say I have a many to many relationship between two entities. I have a join table that has the PK of each parent tables as FK.


Breads
   BreadID PK
   BreadName
   BreadCategoryID

Bakery
   BakeryID PK
   BakeryName
   BakeryAddress

BakeryBread
   BakeryBreadID
   BreadID      FK
   BakeryID     FK

Would creating a clustered index on BakeryBread using
[code]
CREATE CLUSTERED INDEX [CIX_BakeryBread_BreadID_BakeryID] ON [dbo].[BakeryBread]
(
BreadID,
BakeryID

[code]

be a good thing?

Thanks

If you don't have the passion to help people, you have no passion

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-12-16 : 17:39:11

Depends on how you would use the association table. But, I would *guess* you want to change the order to BakeryID, BreadID. Also, any reason for the association table (BakeryBread) to have its own ID? Doesn't the combination of BakeryID and BreadID constitute the Key?

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2010-12-16 : 17:52:10

Lamprey

Thank you. Yes the combo could be the key. BOL says

Consider using a clustered index for queries that do the following:
Use JOIN clauses; typically these are foreign key columns

That was the reason I would use them for when joining the other two tables.

If you don't have the passion to help people, you have no passion

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-12-16 : 18:46:45

When I was asking about BakeryBread "having its own ID" I was referring to the BakeryBreadID column. I assumed that is a PK, but maybe it is not. What I was getting at is that BakeryID, BreadID should be the only columns you need and the BakeryBreadID column is not needed.

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2010-12-17 : 14:54:02

yes BakeryBreadID is the PK. Will have it affect performance or does it break some index rule? ok if I drop it , I do not see any need for it, will I need to make the two other FKs clustered? I assume I will have then I got a good clustered index.
niceeeeee

Thanks!

If you don't have the passion to help people, you have no passion

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources