SQL Server Performance Forum – Threads Archive

database design question

apologies if this is not the right forum but I looked at the others and it seems this is more right than wrong. I’m reviewing a schema design and looking at an ER diagram as the jump off point.
What I see is this for example – happens a lot in other areas of the schema. table contact with key contactid (primary and clustered)
table contactaddress with key contactid,firmid,addressid (primary and clustered)
table address with key addressid (primary and clustered) From looking at just the schema it seems that table contactaddress does not have the correct index – being composite. Any joins to either of the tables will result in a range search at best or a table scan at worst. Can someone please confirm that having contactaddress keyed on contactid clustered and another key on addressid nonclusted would be better? Have I missed something else. thanks

---

If performance isn’t too good, you could always add an identity column and have the clustered index on that instead of on the PK, and/or add non-clustered indexes on more appropriate (combinations of) columns being used for searching. If the system is Contact-oriented, with Firm and Address of secondary interest, then it is fine to have contactid as the first column on a composite index.

---

]]>