Is it a good practice to use GUID in all tables in a database?


  • Please refer


    Please use Mark as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    • ทำเครื่องหมายเป็นคำตอบโดย 沈世鈞 15 กุมภาพันธ์ 2555 1:26
    14 กุมภาพันธ์ 2555 10:19
  • Is it a good practice to use GUID in all tables in a database?

    The short answer to this question is, "NO!"  As you have seen the answer of whether or not to use a GUID at all is "it depends."  The guideline that I would recommend is that you avoid using GUIDs unless you know that you have to.

    There are definite negative performance implications associated with GUIDs; however, there are also times in which it is best to use a GUID.

    As far as the unqualified issue of "use of a GUID in ALL tables":


    • แก้ไขโดย Kent WaldropModerator 14 กุมภาพันธ์ 2555 13:32
    • ทำเครื่องหมายเป็นคำตอบโดย 沈世鈞 15 กุมภาพันธ์ 2555 1:26
    14 กุมภาพันธ์ 2555 13:30
  • In addition to Brian's and Kent's responses...

    If you plan to use GUID as clustered key there are 2 factors you need to consider. First is the size. GUID requires 16 bytes while identity is either 4 or 8 bytes depend on the data type. Second is how to generate value. Random values generated by NEWID() are very bad for performance especially when you're dealing with large (GBs) tables due random IO. For both - clustered and non-clustered index. Those random inserts go to the different part of data files and in most part of the cases data would not be in the cache (think about physical IO). Second, you'll have excessive amount of page splits and huge fragmentation. All of that leads to the very bad performance. 

    NEWSEQUENTIALID() is better than NEWID() - it's kind of sequential (but it reseeds itself from time to time). On the other hand there is no particular reasons to use that instead of identity. 

    I would suggest not to use GUIDs on the large tables in the case if you need to index them. If you're looking for the scenario how to generate unique keys across multiple database consider to use either composite indexes (DBId, RecId) or do some kind of bit masks (bigint where high 4 bytes are DBId and low 4 bytes is unique value with the database).

    If you want to use GUID for security reasons (for example reference it from the query string), think about using 2 columns - indexed ID + non-indexed GUID, 

    Thank you!

    My blog:

    • ทำเครื่องหมายเป็นคำตอบโดย 沈世鈞 15 กุมภาพันธ์ 2555 1:26
    14 กุมภาพันธ์ 2555 14:41
  • Uniqueidentifiers (GUID) are useful in a distributed environment when you plan on rolling up data to a central database for reporting and other analytics. They are also useful when running bi-directional replication or in other cases where you need to synchronized data between two or more databases.

    In a distributed environment, it can prevent PK/AK collisions and when there is a collision across multiple databases, it makes it easier to resolve. However, like everything else, there is a cost in using them. In general I would never cluster on a column that was using uniqueidentifiers, unless it was read-only. 


    William F. Kinsley Healthcare Information Systems Inc.

    • ทำเครื่องหมายเป็นคำตอบโดย 沈世鈞 7 มีนาคม 2555 6:20
    21 กุมภาพันธ์ 2555 22:41
  • Even then, it is probably better to use a server identifier and a composite PK. Why use a GUID which, ultimately, is just a guess, when something that guarantees uniqueness and has some useful information is availible.

    • แก้ไขโดย Brian TkatchEditor 22 กุมภาพันธ์ 2555 13:01
    • ทำเครื่องหมายเป็นคำตอบโดย 沈世鈞 7 มีนาคม 2555 6:21
    22 กุมภาพันธ์ 2555 13:01
  •  There are bad and good things about the GUID.

    Please refer

    Okay, these people both have no Idea how the GUID is stored. Both actually believe it is stored (and processed) as string. No it's NOT. It's stored and processed as a 16 byte or 128 bit integer.

    It's expressed to us humans, for readability reasons, as a hexadecimal String. But that is the same as as with IPv4 or IPv6 adresses: This is only expressions towards the user, because we humans have serious problems reading, memorizing, entering or comparing 32 or 128 digits long bit patterns.

    • ทำเครื่องหมายเป็นคำตอบโดย 沈世鈞 7 มีนาคม 2555 6:20
    4 มีนาคม 2555 12:18
  • get it, GUID is for small table when no primary key

    Quite the opposite.

    I personally use the simple INT with IDENTITY(1,1) for most Columns. For lookupt tables I could propabyl go down to small or tinyint. If int should become to small (wich is unlikely), go to bigint first:
    If bigint becomes to small, you propably run out of RAM or Diskspace first.

    GUID is mostly for the scenario where you have two seperate database, both allow inserts and both have to synchronize.
    Just using INT, Indentity would guarantee the new Primary keys to be duplciate (making any synchronisation impossible or needing a lot of handwork). Or use only even keys in one, wich causes it's own problems (if one has 1 entry and the other 100 at synchronsiation, 200 key values in both are 'lost').
    With GUID's the chance for a duplciated PK is so unbelivable unlikely, that we can take it as non-existant (and leave the one case that is ever going to happen worldwide to manual correction).

    You still could use it, but there are some small perfoance and storage space issues. Nothing near what the "it's stored and processed a string"-people say, but it is there. It's twice the storage and processing time of a bigint.

    • แก้ไขโดย Christopher84 6 มีนาคม 2555 13:52
    • ทำเครื่องหมายเป็นคำตอบโดย 沈世鈞 7 มีนาคม 2555 6:20
    6 มีนาคม 2555 13:49