Concatenated Primary Keys vs. Surrogate Key

Posted on 2007-10-11
Medium Priority
Last Modified: 2011-10-03
Is it ever a good idea to use a concatenated primary key vs. a surrogate primary key?

There are 2 GUIDs being used as a concatenated primary key & I see that as a red flag...thinking about the "fun" of joining to a concatenated primary key.

To me, it would seem if the table is rarely joined it's not that big of a deal & could conserve space in DB (yes, most DBs you don't need to conserve space...but if you dump it to a remote device it could be helpful to have a small DB).   But I still think a surrogate / unique id int primary key is better design.

What do the experts think?   Thanks for your help in advance!
Question by:nespa
  • 2
  • 2

Assisted Solution

Crag earned 600 total points
ID: 20056920
I seem to remember reading something about not using GUIDs as keys, that it was better to use an int column with a unique identify setting.
Check out this link:
LVL 59

Accepted Solution

Jim Dettman (Microsoft MVP/ EE MVE) earned 1400 total points
ID: 20057214
I would think from a performance standpoint, you'd want to use a surrogate (that's basically what they are for).  What I'm still tring to understand though is how you ended up with two GUID's as forming a PK.  By definition, a GUID is a Globaly Unique Identifier; you should only need one.


Author Comment

ID: 20061419
Let me first state that this is not my design.  :)   I'm merely looking at a schema & I couldn't find a valid reason why someone would *not* want a surrogate (again, unless it's to save a modicum of space...such as a database on a mobile phone or something).

Now what I can give you (and probably just answer my own question) is about 50,000 reasons why you *shouldn't* use a GUID... with about 49,998 of them intuitive (there are the two my grandmother came up with - she can't stand GUIDs).    "I can never query the table on the fly using a damn GUID... I always hafta put    select * from myTable where myId = LKSAJDFJ)$#@(UUWIDOKJSVNSEJRLKJK@J#WEWJSLKDJFoiu23

you have to admit, even my grandma makes a good point.

Anyway I like the SQL Performance site - I should have looked to "the bible" (as it should be known) first & then bothered the experts second.  :)   But I did want to double-check & make sure there's not some *funky new train of thought* where GUIDs rule....cause i've been out of db schema land for about 5 months.

And 2 GUIDs were used because they relate to users & folders of users.   each was created using a GUID (UserGuid, FolderGuid), so the UserFolder table has just that - 2 GUIDs as a concatenated PK.    I say create a surrogate and go to starbucks and have a coffee.

...and i hate starbucks.

Expert Comment

ID: 20063384
Does this mean that the question has been answered?

Author Comment

ID: 20064789
i was going to leave it up in case anyone else had ideas/comments...but as mentioned SQL perf. site is usually a really good source of info... even though the solution proposed there is to use a concatenated key of 2 integers.

even in this case i still think a surrogate is a better choice...you can use it to join your inner tables much more easily...and for outside references (i.e. when you have the 2 integers mentioned above & that's how you need to find a record) you can always query the table with that info to determine the surrogate id used.   and all you have to do is check the table first to make sure that int1/int2 combo does not exist in the table first before inserting.

hope that makes sense.

anyway thanks for the help!

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

578 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question