locked
To GUID or not to GUID, that is the question. RRS feed

  • Question

  • So heres the deal.. Ive got a database with about 35 tables in it. Each table consists of about 5-15 columns, 1 has around 30 columns. All the tables have the primary key as uniqueidentifier..

    The database is for storing personal records of such..

    My question is this.

    If I were to ditch the GUID as the primary key and make a composite key of INT and the serverid, how does one get the serverid? Is it a hardcoded value, im really lost here.. I want to ditch the GUID's but until I can make sense of this composite key with the server id im stuck.

    Any ideas, suggestions, comments would be greatly appreciated.

    If rebuilding the database is what has to be done, then so be it.


    Here is an example of my current table stucture

    Profiles Table
    ID / GUID (PK)
    Name
    DOB
    HEIGHT
    WEIGHT

    Allergies Table
    ID / GUID (PK)
    ProfileID / GUID (FK)
    Allergy
    ect..
    ect..


    How would I duplicate this using the composite key with INT and serverid???

    Thanks everyone!

    ~Matt
    Friday, February 19, 2010 8:04 PM

Answers

  • In that case you don't have to worry about ServerID right now.

    Just use INT IDENTITY(1,1) for Primary Key. It is much faster and simpler than GUID.



    Kalman Toth, SQL Server & BI Training, SSAS OLAP, SSIS, SSRS; http://www.SQLUSA.com
    • Marked as answer by intertek Saturday, February 20, 2010 11:56 PM
    Saturday, February 20, 2010 5:54 AM

All replies

  • Since you mention ServerID, I will assume that this is a distributed database, with multiple servers each hosting a database with the same schema, and you want to come up with an approach to key generation on each server that would generate unique keys across all servers. If so, please take a look at this post, which describes how to do that without using GUID-based keys or compound keys:
    http://blogs.msdn.com/dfurman/archive/2009/08/30/surrogate-keys-in-distributed-databases.aspx
    Friday, February 19, 2010 8:15 PM
  • Thank you Dimitri.

    I will have a look.
    Saturday, February 20, 2010 12:49 AM
  • Assume that there is a central Server table .  Since ServerID is static on each server it can just be hard-wired without FK constraint.

    Then the composite key:

    Profiles Table
    ProfileID INT IDENTITY(1,1) (PK)
    ServerID INT         (PK)
    Name
    DOB
    HEIGHT
    WEIGHT
    
    Allergies Table
    ID INT IDENTITY(1,1) (PK)
    ServerID INT  (FK)       (PK)
    ProfileID / INT (FK)
    Allergy
    

    Kalman Toth, SQL Server & BI Training, SSAS OLAP, SSIS, SSRS; http://www.SQLUSA.com
    Saturday, February 20, 2010 12:49 AM
  • Thank you Kalman,

    So, am I correct in assuming that I would have to set this serverid somewhere on each server that the database is deployed to? If so where? My stored procedures?

    Another question,

    So lets say I wanted to get all the Allergies for a specified profile, does the client have to know the server id?

    Or do I just query like normal "Select ID, Allergy FROM Allergies WHERE ProfileID = @ProfileID"

    How does this work???



    Thanks again for your time and patience fellas.. It is very much appreciated.


    ~Matt
    Saturday, February 20, 2010 1:19 AM
  • One way is just to keep and maintain the Server table centrally. When ServerOmega entered into the table it may get ID 77 which then can be hard-wired update in the tables on ServerOmega.

    If the application requires the Server table on each server, then a replication mechanism must be put in place.

    >So lets say I wanted to get all the Allergies for a specified profile, does the client have to >know the server id?

    The only time the ServerID needed along with the ID PK when in the distributed application the data has to be tagged by the originating server.

    For example on ServerOmega you have ProfileID 11 and same on ServerTheta. The unique composite ID would be a pair of integers

    ProfileID   ServerID
    11            77
    11            44



    Kalman Toth, SQL Server & BI Training, SSAS OLAP, SSIS, SSRS; http://www.SQLUSA.com
    Saturday, February 20, 2010 1:49 AM
  • Thank you Kalman for your reply.

    A few questions though,

    What do you mean by "tagged"?

    Sorry, im still a newb.. Don't know all the terms yet!

    Also, Is Profile 11 is same on both servers, just has a different serverid?

    So If I wanted to update it I could just "Update Profiles Set Name = 'Matt' WHERE ID = 11" ??


    Basically what Im hoping to do is set up a database that will work on a single server, that could be easily deployed to other servers without having to make any changes to the structure..

    Also, I would like to not have to worry about server ids and all that stuff.. I just want to query the table using the 1 id.


    Would it be best to just stick with the GUID and not have to worry about all the server ids and just use the NEWSEQUENTIALID??

    Also, I dont know much about indexing, I have read that guid is bad for primary key when it comes to indexing, are there any guru's out there that can solve this problem and are willing to work? Let me know!




    If I may ask, what method do the rest of you use? 




    ~Matt
    Saturday, February 20, 2010 2:08 AM
  • Matt,

    How many servers are there? Distributed in US? World?

    Assume there is a server in Chicago, in the central Server table it got the ID 22.
    Another server in San Francisco with ID 23.

    As entries are made in the databases on the two servers, the ProfileID-s keep incrementing starting at 1.

    So eventually there will be ProfileID= 11 in Chicago and similarly in San Francisco.

    If you have to transmit this data to headquarters in New York, you have to "tag" it with the ServerID, otherwise HQ would have 2 ProfileID 11 without knowing the origin.

    So in the database in NY:

    ProfileID     ServerID
    11              22
    11              23

    The composite key (ProfileID, ServerID) would be the Primary Key which would uniquely identify each record within the enterprise.

    INT is much faster in indexing than GUID. But that is a secondary issue right know. Even if you use GUID, you still need a ServerID. You need to know where is the data coming from?



    Kalman Toth, SQL Server & BI Training, SSAS OLAP, SSIS, SSRS; http://www.SQLUSA.com
    Saturday, February 20, 2010 2:40 AM
  • Thanks again Kalman for your reply.

    To be honest, I dont even know if the database needs to be distributed.. There is 1 server right now. But i was thinking if the database got really big it would have to be distributed.

    How do I know if my database needs to be distributed?



    Lets say I change all the guid's to int, and later on the database grows and needs to be distributed or whatever to balance the load and size.

    Would all I would have to do is add a serverid column? And start adding more database servers?

    Let me know your thoughts.


    ~Matt
    Saturday, February 20, 2010 4:31 AM
  • In that case you don't have to worry about ServerID right now.

    Just use INT IDENTITY(1,1) for Primary Key. It is much faster and simpler than GUID.



    Kalman Toth, SQL Server & BI Training, SSAS OLAP, SSIS, SSRS; http://www.SQLUSA.com
    • Marked as answer by intertek Saturday, February 20, 2010 11:56 PM
    Saturday, February 20, 2010 5:54 AM
  • Okay, Thanks..

    Saturday, February 20, 2010 6:02 AM
  • Did I miss something? It failed to solve my problem.

    Can you describe your problem in detail? Thanks.
    Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM
    Saturday, August 14, 2010 5:59 AM