GUID or IDENTITY...The millionaire dollar question

Bobby P 221 Reputation points
2023-01-25T19:43:50.7333333+00:00

One of our clients is proposing an Update Table with a GUID identifier. The updates are fairly infrequent and will NOT be in the thousands...well maybe in the thousands at some point but certainly not in the 10s of thousands.

Is that the reasoning behind a GUID proposed ID rather than an IDENTITY column? That and portability perhaps. We will have the need to report on updates from several tables...Address...Identification...all associated with the same UpdateGUID to keep the Update together.

Can anyone poke any holes in this proposal? Is there any reason to push back on the proposed GUID? Will I be able to write a SSRS Report and accompanying query that will run fast utilizing the associated UpdateGUID ID across several tables to report on the changes?

Thanks for your review and am hopeful for a reply.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,811 questions

4 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Bobby P 221 Reputation points
    2023-01-26T13:51:08.17+00:00

    They want us to store changes that a Patient makes to their information...Addressing...Allergies...Diseases...

    So the Allergy changes and Disease changes imply a one-to-many relationship.

    The Patient updates will come over via an API. We have been asked to log those changes and report on them. The client is proposing GUIDs as IDs. So we would capture the Patient Updates by a GUID that we have passed them upon enrollment. They would then pass us back the update via the API.

    The client is proposing GUIDs for all of these IDs. I know GUIDs are not great for a clustered index.

    So I was just looking for the Pros and Cons of using GUIDs as opposed to like an IDENTITY column.

    I did find this web site...

    [https://www.sqlshack.com/understanding-the-guid-data-type-in-sql-server/

    Which kind of explains the whole GUID concept

    Just looking for more content and opinion regarding this is all.

    0 comments
  2. Bobby P 221 Reputation points
    2023-01-26T13:56:56.85+00:00

    So we have a client that wants us to capture and report on any changes a Patient makes to their information that might include the following...

    • Address
    • Phone
    • Allergy
    • Disease

    Allergy and Disease changes imply a one-to-many relationship so we are proposing separate tables for those changes.

    The client will pass us back any Patient changes via an API with a PatientGUID that we have passed to them via Enrollment.

    We will capture these Patient updates and then report on them.

    The client is proposing GUID IDs for everything and I know GUIDs are not very conducive for a clustered index.

    We did find this web site which kind of explains the whole GUID concept in detail...

    [https://www.sqlshack.com/understanding-the-guid-data-type-in-sql-server/

    So we are just looking for some opinions and additional content regarding the pros and cons of GUIDs and why use a GUID over like an IDENTITY column

    Any opinion, additional content, web sites, YouTubes etc. would be greatly appreciated.

    Thanks.

    0 comments
  3. Erland Sommarskog 101.8K Reputation points MVP
    2023-01-26T22:21:56.2933333+00:00

    There is an important difference between GUID and IDENTITY: IDENTITY must be generated in SQL Server, GUIDs can be generated anywhere. I don't know if it matters in this case, but if the other system sends you a package of updates, and want to add an ID to this package, they can generate a GUID, but an running integer value may be more difficult, if this package is generated client side.

    Now, you say "with a PatientGUID that we have passed to them via Enrollment.", so in this case is the ID is generated on your side, and in that case, ID or GUID are all the same. There may still be a reason why they want a GUID, but if you already have IDENTITY values, I would be inclined to stick with that, at least until they present a good argument why a GUID would be better.

    I know GUIDs are not very conducive for a clustered index.

    Nah, that depends on whom you talk to. Jeff Moden has a great presentation which puts this entire discussion on its head. But, admittedly, it's advanced material, and it's not something I would recommend an accidental DBA who mainly focus on the development side of things.

    0 comments
  4. PercyTang-MSFT 12,426 Reputation points Microsoft Vendor
    2023-01-27T06:50:49.7633333+00:00

    Hi @Bobby P

    I found a post about what GUIDs are and where to use them, you can refer to it.

    https://stackoverflow.com/questions/371762/what-exactly-is-guid-why-and-where-i-should-use-it

    Best regards,

    Percy Tang

    If the answer is the right solution, please click "Accept Answer". If you have extra questions about this answer, please click "Comment".

    Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments