none
What is the perfect data type to use as a primary key RRS feed

  • Question

  • Hi I just want to ask what is the best datatype for a primary key. Usually I used int (auto increment) but now i want to know when to choose int or GUID as my primary key.

    Thanks

    Tuesday, June 25, 2019 6:25 AM

Answers

  • Hi TinVin727,

     

    Int is the best choice for primary key, if you are certain about performance and you are not planning to replicate or merge records, then use int.On this data type you can define AUTO_INCREMENT, and computations are also performing fast.

     

    If you're going to be doing any syncing between databases , then you should be using GUIDs for your primary keys.

     

    GUID Pros

    Unique across every table, every database and every server

    Allows easy merging of records from different databases

    Allows easy distribution of databases across multiple servers

    You can generate IDs anywhere, instead of having to roundtrip to the database

    Most replication scenarios require GUID columns anyway

    GUID Cons

    It is a whopping 4 times larger than the traditional 4-byte index value; this can have serious performance and storage implications if you're not careful

    Cumbersome to debug (where userid='{BAE7DF4-DDF-3RG-5TY3E3RF456AS10}')

     

    Hope this could help you .

    Best regards,

    Dedmon Dai


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Marked as answer by TinVin727 Monday, July 8, 2019 1:19 AM
    Wednesday, June 26, 2019 3:24 AM

All replies

  • I would go for INT and see why

    It is 4 times larger than the traditional 4-byte index value; this can have serious performance and storage implications 

    I am not yest talking about  a fragmentation for GUID

    https://www.mssqltips.com/sqlservertip/5105/sql-server-performance-comparison-int-versus-guid/


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Tuesday, June 25, 2019 7:40 AM
    Moderator
  • Hi TinVin727,

     

    Int is the best choice for primary key, if you are certain about performance and you are not planning to replicate or merge records, then use int.On this data type you can define AUTO_INCREMENT, and computations are also performing fast.

     

    If you're going to be doing any syncing between databases , then you should be using GUIDs for your primary keys.

     

    GUID Pros

    Unique across every table, every database and every server

    Allows easy merging of records from different databases

    Allows easy distribution of databases across multiple servers

    You can generate IDs anywhere, instead of having to roundtrip to the database

    Most replication scenarios require GUID columns anyway

    GUID Cons

    It is a whopping 4 times larger than the traditional 4-byte index value; this can have serious performance and storage implications if you're not careful

    Cumbersome to debug (where userid='{BAE7DF4-DDF-3RG-5TY3E3RF456AS10}')

     

    Hope this could help you .

    Best regards,

    Dedmon Dai


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Marked as answer by TinVin727 Monday, July 8, 2019 1:19 AM
    Wednesday, June 26, 2019 3:24 AM
  • Thanks Dedmon, for the pros and cons, maybe i study about the replication further to really understand it.
    Monday, July 8, 2019 1:20 AM