Primary Key

Question

Primary Key

texpic

SSCertifiable

Points: 5887
More actions
October 15, 2006 at 5:22 pm

#92517

Lets say I have two tables: AccountData and AccountManager. I set my primary key AcctNo + AcctManager in the table AccountData. In AccountManager I set the primary key AcctManager.
This may seem like a lame question, but is AcctManager in AccountData indexed for purposes of a join with AccountManager? Thanks!
AccountData:
AcctNo
AcctManager
Etc
AccountManager:
AcctManager
ManagerName

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Ian Griffiths Ten Centuries Points: 1025 More actions · Answer 1

There will be a unique index on the AccountData table containing the AcctNo and AcctManager fields. If the AcctNo field comes first in the index, it won't be optimal for the join you're talking about. If AcctManager comes first in the index, it will be. However, I'm sure you have other queries which would prefer it the other way around, i.e. you've got an account number and wish to find the manager. In that case you may choose to have two indexes on the AccountData table, one with AcctNo and AcctManager, the other with AcctManager and AcctNo. Be wary of doing that if there are a lot of inserts on that table though.

texpic SSCertifiable Points: 5887 More actions · Answer 2

Thanks. In table AccountData AcctNo is unique. By adding AcctManager to the primary key it sounds like I have not gained any indexing benefit (or do I gain some benefit, just not as much as if it was first in the primary key)?

AccountData:

AcctNo

AcctManager

Ian Griffiths Ten Centuries Points: 1025 More actions · Answer 3

I'd expect there to be some benefit, but impossible to know without checking the execution plans for both versions, i.e. with AcctManager in the key and without. I'd remove AcctManager from the key if it doesn't do anything to uniquely identify an account (which is all a primary key is supposed to do) and check the exection plans for a join without any additional indexes, and with an additional, non-unique index on AcctMagaer on the AccountData table.