• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 822
  • Last Modified:

Concatenated Primary Keys vs. Surrogate Key

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!
  • 2
  • 2
2 Solutions
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:
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
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.

nespaAuthor Commented:
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.
Does this mean that the question has been answered?
nespaAuthor Commented:
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!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now