none
using GUID / uniqueidentifier as primary key

    Question

  • <BACKGROUND INFO>  We are in the database design stage of creating a db and we plan to use a GUID as the primary key for our tables (the app will be a smart client and will need to support offline functionality so when the app comes back online and the queued up inserts come thru we want to ensure that the primary keys are unique).  We plan to have the app generate the guid  and to send the GUID over as a parameter i.e.

    <code>GUID myPrimaryKey = GUID.NewID(); </code>

    and will not be using the DEFAULT NEWID clause on the CREATE TABLE statements. 
    </BACKGROUND INFO>

    <QUESTION > Is it better to make the data type for the primary key columns to be uniqueidentifier or should the datatype be varchar(36).</QUESTION>

    I only ask this because the tables have already been created with the primary key columns having a datatype of varchar(36) and changing them would be a little bit of work.  Not a big deal but don't want to do it if we don't have to.  If its really advantageous we will go ahead and do it but if not we would like to leave it as is.  Thanks for your thoughts!

    Bo
    Thursday, July 06, 2006 4:11 PM

Answers

  • If you must use a GUID as your key, then you're much better off using the GUID data type, rather than varchar. A GUID is basically a big number, and it would be more efficient to search by GUID than a string.

    You can also take advantage of replciation features of having a unique GUID in the table, by setting the column to RowGUID. You'll also save some datatype conversion issues. Further, having varchar(36) wouldn't prevent you entering invalid GUID values, such as  "WWWXXXY%^AFB66d974A+-*FDD4108C8".

    I'm sure there are even more reasons, but the above few are compelling enough to make the change.
    Thursday, July 06, 2006 4:44 PM

All replies

  • If you must use a GUID as your key, then you're much better off using the GUID data type, rather than varchar. A GUID is basically a big number, and it would be more efficient to search by GUID than a string.

    You can also take advantage of replciation features of having a unique GUID in the table, by setting the column to RowGUID. You'll also save some datatype conversion issues. Further, having varchar(36) wouldn't prevent you entering invalid GUID values, such as  "WWWXXXY%^AFB66d974A+-*FDD4108C8".

    I'm sure there are even more reasons, but the above few are compelling enough to make the change.
    Thursday, July 06, 2006 4:44 PM
  • In addition to the above post, your not really creating a primary key, your basically creating an application controlled column, which just forces unique values. A well built primary key should be generated by the server not an application or user inserting into it. Also as a varchar (35) your able to insert any value be it one character or 20 and it will be accepted. Unless your planning to do maintaince checks / scans on your primary key column you could misplace/lose data should your application not generate a correct GUID.

    Not sure how your application queues but when it transitions to sending to the server let the server handle the guid generation. If you feel though that your application will never fail or the piece which generates the GUID will never be altered then you should be ok. Just remember the first item you set in stone is the first place you have to change. :)

    Tuesday, July 11, 2006 1:16 PM