Concatenated Primary Keys vs. Surrogate Key

Posted on 2007-10-11
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
    LVL 5

    Assisted Solution

    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 56

    Accepted Solution

    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.

    LVL 1

    Author Comment

    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.
    LVL 5

    Expert Comment

    Does this mean that the question has been answered?
    LVL 1

    Author Comment

    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 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

    What Security Threats Are You Missing?

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Join & Write a Comment

    Suggested Solutions

    Title # Comments Views Activity
    Need help creating a stored procedure 4 47
    Multiple Max Statements 11 21
    Help with SQL joins 9 32
    by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
    Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
    This video is in connection to the article "The case of a missing mobile phone (". It will help one to understand clearly the steps to track a lost android phone.
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    728 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

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now