In recent years I was using MSSQL databases, and all unique records in tables has the ID column type of bigint (long). It is autoincrementing and generally - works fine.
近年来我使用的是MSSQL数据库，表中所有唯一记录的ID列类型都是bigint（long）。这是自动增量，一般 - 工作正常。
Currently I am observing people prefer to use GUIDs for record's identity.
目前我观察人们更喜欢使用GUID来记录身份。
Does it make sense to swap bigint to guid for unique record id?
将bigint交换为guid以获取唯一记录ID是否有意义？
I think it doesn't make sense as generating bigint as well as sorting would be always faster than guid, but... some troubles come when using two (or more) separated instances of application and database and keep them in sync, so you have to manage id pools between sql servers (for example: sql1 uses id's from 100 to 200, sql2 uses id's from 201 to 300) - this is a thin ice. With guid id, you don't care about id pools.
我认为没有意义，因为生成bigint以及排序总是比guid更快，但是...当使用两个（或更多）分离的应用程序和数据库实例并使它们保持同步时会遇到一些麻烦，所以你必须管理sql服务器之间的id池（例如：sql1使用从100到200的id，sql2使用从201到300的id） - 这是一个薄薄的冰。使用guid id，您不关心id池。
What is your advice for my mirrored application (and db): stay with traditional ID's or move to GUIDs?
您对我的镜像应用程序（和数据库）的建议是什么：保留传统ID或转移到GUID？
Thanks in advance for your reply!
在此先感谢您的回复！
5 个解决方案
#1
8
guids have the
guids有
Advantages:
优点：
- Being able to create them offline from the database without worrying about collisions.
- 能够从数据库脱机创建它们而不必担心冲突。
- You're never going to run out of them
- 你永远不会用完它们
Disadvantages:
缺点：
- Sequential inserts can perform poorly (especially on clustered indexes).
- Sequential Guids fix this
- Sequential Guids解决了这个问题
- 顺序插入可能表现不佳（尤其是在聚簇索引上）。 Sequential Guids解决了这个问题
- Take up more space per row
- 每行占用更多空间
- creating one cleanly isn't cheap
- but if the clients are generating them this is actually no problem
- 但如果客户端正在生成它们，这实际上没问题
- 干净地创建一个并不便宜，但如果客户正在生成它们，这实际上没有问题
The column should still have a unique constraint (either as the PK or as a separate constraint if it is part of some other relationship) since there is nothing stopping someone supplying the GUID by hand and accidentally/deliberately violating uniqueness.
该列仍应具有唯一约束（作为PK或作为单独约束，如果它是某些其他关系的一部分），因为没有什么能阻止某人手动提供GUID并且意外/故意违反唯一性。
If the space doesn't bother you and your performance if not significantly impacted they make a lot of problems go away. The decision is inevitably specific to the individual needs of the application.
如果空间没有打扰你，你的表现如果没有受到显着影响，他们会让很多问题消失。该决定不可避免地针对应用程序的个性化需求。
#2
3
I use GUIDs in any scenario that involves either replication or client-side ID generation. It's just so much easier to manage identity in either of those situations with a GUID.
我在任何涉及复制或客户端ID生成的场景中使用GUID。在使用GUID的任何一种情况下管理身份都要容易得多。
For two-tier scenarios like a web application talking directly to the database, or for servers that don't need to replicate (or perhaps only need to replicate in one direction, pub/sub style) then I think an auto-incrementing ID column is just fine.
对于双层场景，例如直接与数据库通信的Web应用程序，或者不需要复制的服务器（或者可能只需要在一个方向上复制，pub / sub样式），我认为自动递增ID列很好。
As for whether to stay with autoincs or move to GUIDs ... it's one thing to advocate GUIDs in a green-field application where you can make all these decisions up front. It's another to advise someone to migrate an existing database. It might be more pain than it's worth.
至于是否继续使用autoincs或转移到GUIDs ......在绿色领域的应用程序中提倡GUID是一回事，你可以预先做出所有这些决定。建议某人迁移现有数据库是另一回事。它可能比它的价值更痛苦。
#3
2
GUIDs have issues with performance and concurrency when page splits occur. INTs can run page fill at 100% - only added at one end, GUIDS add everywhere so you probably have to run a lower fill - which wastes space throughout the index.
发生页面拆分时，GUID会出现性能和并发性问题。 INT可以100％运行页面填充 - 仅在一端添加，GUIDS在任何地方添加，因此您可能必须运行较低的填充 - 这会浪费整个索引的空间。
GUIDS can be allocated in the application, so the App can know the ID of the record it will have created, which can be handy; but, technically, it is possible for duplicate GUIDs to be generated (long odds, but at least put a Unique Index on GUID columns)
GUIDS可以在应用程序中分配，因此App可以知道它将创建的记录的ID，这可以很方便;但是，从技术上讲，可能会生成重复的GUID（长赔率，但至少在GUID列上放置一个唯一索引）
I agree for merging databases its easier. But for me a straight INT is better, and then live with the hassle of sorting out how to merge DBs when/if it is actually needed.
我同意合并数据库更容易。但对我来说，直接的INT更好，然后在解决如何合并DB时（如果实际需要的话）的麻烦。
#4
1
If your data move around often, then GUID is the best one for the Key of the table. If you really care about the performance, just stick to int or bigint
如果您的数据经常移动，那么GUID是表的Key的最佳选择。如果你真的关心性能，只需坚持使用int或bigint
If you want to leverage both of above, use int or bigint as the key of the table and each row can have a rowguid column so that the data can also be moved around easily without losing integrity.
如果要利用上述两者，请使用int或bigint作为表的键，每行可以有一个rowguid列，以便数据也可以轻松移动而不会丢失完整性。
#5
0
If the ids are going to be displayed in the querystring, use Guids, otherwise use long as a rule.
如果要在查询字符串中显示ID，请使用Guids，否则请使用long作为规则。
#1
8
guids have the
guids有
Advantages:
优点：
- Being able to create them offline from the database without worrying about collisions.
- 能够从数据库脱机创建它们而不必担心冲突。
- You're never going to run out of them
- 你永远不会用完它们
Disadvantages:
缺点：
- Sequential inserts can perform poorly (especially on clustered indexes).
- Sequential Guids fix this
- Sequential Guids解决了这个问题
- 顺序插入可能表现不佳（尤其是在聚簇索引上）。 Sequential Guids解决了这个问题
- Take up more space per row
- 每行占用更多空间
- creating one cleanly isn't cheap
- but if the clients are generating them this is actually no problem
- 但如果客户端正在生成它们，这实际上没问题
- 干净地创建一个并不便宜，但如果客户正在生成它们，这实际上没有问题
The column should still have a unique constraint (either as the PK or as a separate constraint if it is part of some other relationship) since there is nothing stopping someone supplying the GUID by hand and accidentally/deliberately violating uniqueness.
该列仍应具有唯一约束（作为PK或作为单独约束，如果它是某些其他关系的一部分），因为没有什么能阻止某人手动提供GUID并且意外/故意违反唯一性。
If the space doesn't bother you and your performance if not significantly impacted they make a lot of problems go away. The decision is inevitably specific to the individual needs of the application.
如果空间没有打扰你，你的表现如果没有受到显着影响，他们会让很多问题消失。该决定不可避免地针对应用程序的个性化需求。
#2
3
I use GUIDs in any scenario that involves either replication or client-side ID generation. It's just so much easier to manage identity in either of those situations with a GUID.
我在任何涉及复制或客户端ID生成的场景中使用GUID。在使用GUID的任何一种情况下管理身份都要容易得多。
For two-tier scenarios like a web application talking directly to the database, or for servers that don't need to replicate (or perhaps only need to replicate in one direction, pub/sub style) then I think an auto-incrementing ID column is just fine.
对于双层场景，例如直接与数据库通信的Web应用程序，或者不需要复制的服务器（或者可能只需要在一个方向上复制，pub / sub样式），我认为自动递增ID列很好。
As for whether to stay with autoincs or move to GUIDs ... it's one thing to advocate GUIDs in a green-field application where you can make all these decisions up front. It's another to advise someone to migrate an existing database. It might be more pain than it's worth.
至于是否继续使用autoincs或转移到GUIDs ......在绿色领域的应用程序中提倡GUID是一回事，你可以预先做出所有这些决定。建议某人迁移现有数据库是另一回事。它可能比它的价值更痛苦。
#3
2
GUIDs have issues with performance and concurrency when page splits occur. INTs can run page fill at 100% - only added at one end, GUIDS add everywhere so you probably have to run a lower fill - which wastes space throughout the index.
发生页面拆分时，GUID会出现性能和并发性问题。 INT可以100％运行页面填充 - 仅在一端添加，GUIDS在任何地方添加，因此您可能必须运行较低的填充 - 这会浪费整个索引的空间。
GUIDS can be allocated in the application, so the App can know the ID of the record it will have created, which can be handy; but, technically, it is possible for duplicate GUIDs to be generated (long odds, but at least put a Unique Index on GUID columns)
GUIDS可以在应用程序中分配，因此App可以知道它将创建的记录的ID，这可以很方便;但是，从技术上讲，可能会生成重复的GUID（长赔率，但至少在GUID列上放置一个唯一索引）
I agree for merging databases its easier. But for me a straight INT is better, and then live with the hassle of sorting out how to merge DBs when/if it is actually needed.
我同意合并数据库更容易。但对我来说，直接的INT更好，然后在解决如何合并DB时（如果实际需要的话）的麻烦。
#4
1
If your data move around often, then GUID is the best one for the Key of the table. If you really care about the performance, just stick to int or bigint
如果您的数据经常移动，那么GUID是表的Key的最佳选择。如果你真的关心性能，只需坚持使用int或bigint
If you want to leverage both of above, use int or bigint as the key of the table and each row can have a rowguid column so that the data can also be moved around easily without losing integrity.
如果要利用上述两者，请使用int或bigint作为表的键，每行可以有一个rowguid列，以便数据也可以轻松移动而不会丢失完整性。
#5
0
If the ids are going to be displayed in the querystring, use Guids, otherwise use long as a rule.
如果要在查询字符串中显示ID，请使用Guids，否则请使用long作为规则。