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

答案

  • Please refer 

    http://msdn.microsoft.com/en-us/library/ms190348.aspx

    http://msdn.microsoft.com/en-us/library/ms187942.aspx


    Thanks
    Manish

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

    • 已标记为答案 沈世鈞 2012年2月15日 1:26
    2012年2月14日 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":

    NO



    2012年2月14日 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: http://aboutsqlserver.com

    • 已标记为答案 沈世鈞 2012年2月15日 1:26
    2012年2月14日 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. 

     Bill


    William F. Kinsley Healthcare Information Systems Inc.

    • 已标记为答案 沈世鈞 2012年3月7日 6:20
    2012年2月21日 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.


    2012年2月22日 13:01
    答复者
  •  There are bad and good things about the GUID.

    Please refer 

    http://blog.sqlauthority.com/2010/04/28/sql-server-guid-vs-int-your-opinion/

    http://www.sql-server-performance.com/2005/guid-performance/

    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.

    • 已标记为答案 沈世鈞 2012年3月7日 6:20
    2012年3月4日 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: http://msdn.microsoft.com/en-us/library/ms187745.aspx
    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.



    2012年3月6日 13:49

全部回复

  •  There are bad and good things about the GUID.

    Please refer 

    http://blog.sqlauthority.com/2010/04/28/sql-server-guid-vs-int-your-opinion/

    http://www.sql-server-performance.com/2005/guid-performance/


    Thanks
    Manish

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

    2012年2月14日 9:46
  • any example or situation that must use GUID?
    2012年2月14日 9:53
  • Please refer 

    http://msdn.microsoft.com/en-us/library/ms190348.aspx

    http://msdn.microsoft.com/en-us/library/ms187942.aspx


    Thanks
    Manish

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

    • 已标记为答案 沈世鈞 2012年2月15日 1:26
    2012年2月14日 10:19
  • It depends if you need them. The question is then: Why do you want to use a GUID
    2012年2月14日 13:23
    答复者
  • 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":

    NO



    2012年2月14日 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: http://aboutsqlserver.com

    • 已标记为答案 沈世鈞 2012年2月15日 1:26
    2012年2月14日 14:41
  • get it, GUID is for small table when no primary key

    2012年2月15日 1:27
  • get it, GUID is for small table when no primary key


    ( no )
    2012年2月15日 12:52
    版主
  • 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. 

     Bill


    William F. Kinsley Healthcare Information Systems Inc.

    • 已标记为答案 沈世鈞 2012年3月7日 6:20
    2012年2月21日 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.


    2012年2月22日 13:01
    答复者
  •  There are bad and good things about the GUID.

    Please refer 

    http://blog.sqlauthority.com/2010/04/28/sql-server-guid-vs-int-your-opinion/

    http://www.sql-server-performance.com/2005/guid-performance/

    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.

    • 已标记为答案 沈世鈞 2012年3月7日 6:20
    2012年3月4日 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: http://msdn.microsoft.com/en-us/library/ms187745.aspx
    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.



    2012年3月6日 13:49