Is UniqueIdentifier will be a good choice for Primarykey

Sajith A K 20 Reputation points
2023-11-22T14:42:37.08+00:00

Hello,

"Is UniqueIdentifier will be a good choice for Primarykey".

As I know that Uniqueidentifier identifier comparison will take more time than a number comparison. I can see a lot of issues on Uniqueidentifier.

In our projects, most of the tables are using Uniqueidentifier as PK and also combination of Uniqueidentifier + BigInt as PK. Most of the database calls are happening using entity framework.

Is Uniqueidentifier as PK will cause any issue ? Can you please provide a right info on the same.

Azure SQL Database
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,670 questions
Accepted answer
  1. PercyTang-MSFT 12,501 Reputation points Microsoft Vendor
    2023-11-23T01:58:51.07+00:00

    Hi @Sajith A K

    Using UniqueIdentifier as a primary key has some advantages and disadvantages, depending on your requirements.

    Advantage:

    UniqueIdentifier is a 16-byte GUID (Globally Unique Identifier) that is guaranteed to be unique across tables, databases, servers, and even applications. This makes it useful for scenarios where you need to merge or replicate data from different sources, or expose keys in URLs or APIs without risking collisions or guessing attacks.

    Disadvantage:

    UniqueIdentifier is much larger than a typical integer-based primary key, which means it takes more space and can affect the performance of queries, indexes, and joins. It also makes the rows less readable and harder to type or remember.

    Best regards,

    Percy Tang

    0 comments

2 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Alberto Morillo 33,611 Reputation points MVP
    2023-11-22T15:18:10.52+00:00

    The problem with GUIDs is they have a size and selectivity problem.

    If the GUID is generated from the application code, they will be generated completely random and create fragmentation. SQL Server has the option to create a new sequential ID.

    GUIDs can result in more page splits and increased fragmentation, and SQL Server will need more pages to process your query. GUIDs are a lot wider than an integer value.

    GUIDs are also very inefficient in join operations because they are very large values.

    0 comments
  2. Bruce (SqlWork.com) 64,161 Reputation points
    2023-11-22T16:42:19.04+00:00

    GUIDS are not converted to varchar to compare. they are 16 byte binary values (twice the size of a bigint). while fragmentation may be an issue, the benefits of using a GUID

    • they can be preassigned by the client. very handy for relationships and may improve insert performance
    • they can prevent hot spot insertion points and prevent deadlocks, and improve insert performance
    • testing shows no real performance difference if indexes maintained

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.