Multi column primary key, which column to use as leading edge?

Given SQL SERVER 2000 and table:

CREATE TABLE Person
(
  CityID int not null,
  PersonID bigint not null,
  SomeOtherAttributes null
)
WHERE CityID is a FK to Cities table,  and PersonID is only unique within a city (Off topic but FYI - the PersonId is imported from another application which does not use globally unique identifiers).

The primary key options are:
 (CityId,PersonId)
or
 (PersonId,CityId)

My question is which primary key to specify and why/how to figure out which primary key to specify?

Thank you!

 
LVL 7
mjmarlowAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

LowfatspreadCommented:
cityid,personid maybe of use...

but what is the cardinality of the two columns (how many values )

what do you normally search with city ? or person ?

to a cetain extent it may not matter unless you are intending to cluster in the key as well...
 

 
0
NightmanCTOCommented:
PersonID, CityId

As a general principle, you should have your most selective column first.
0
lahousdenCommented:
Both keys will serve well as PK.  However, there are some secondary considerations.  For example, if you see situations where selections are going to ask for all Person's in a particular City, or situations where reports on the Person table are to be ordered by CityID, then if you put CityId first in the Primary Key (like your first option) the query optimiser will be able to leverage the index that the Primary Key is based on, giving some performance benefits in these situations without requiring you to create an additional index on the table.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

mjmarlowAuthor Commented:
Some of my more complex queries  include a "CityId" qualifier in the join or where clause.   I noticed that  SQL Server index optimizer recommended then added an Index "CityId" on my Person table.  I wonder if I would spare the extra index overhead if i used CityId as the leading edge instead (of following the book of using most selective column).
0
NightmanCTOCommented:
That suggests that most of your queries are by CityId instead of PersonId. If that is the case, CityId would be the better option, as explained by lahousden
0
mjmarlowAuthor Commented:
Lowfatspread:

Cardinality:
Persons: 705151
Cities: 159

I often search for a subset  of (5-1000) persons who are isolated by City and presence in another table via a join on CityId and PersonId.

I do not intend on clustering the key, but from what i have read, clustering is done automatically if there are no other clustered indexes specified.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.