SQL Server GUID（来自 Active Directory）与 Int
我们从旧的订购系统中迁移了大量数据。 该系统将用户创建的每个订单的姓名缩写存储在我们的“订单”表中。 现在我们有了一个查看活动目录的视图，我想使用活动目录 objectguid（或引用该 guid 的内容）更新这些缩写。 这将使我们能够更改活动目录中的用户姓名缩写，而不必担心更新“Orders”表记录。
我读到，使用 guid 与 int 时索引性能很差。 可能解决此问题的一种方法是使用一个将 guid 映射到 int 的表，然后将 int 值存储在我们的订单表中。 这是个好主意吗？ 有什么想法吗？
We migrated a lot of data from our old ordering system. This system stored the users initials in our "Orders" table for each order they created. Now that we have a view that looks at active directory I would like to update those initials with the active directory objectguid (or something that references the guid). This would allow us to change the users initials in active directory without having to worry about updating the "Orders" table records.
I've read that index performance is lackluster when using guids vs ints. One way to possibly solve this is have a table that maps guids to ints then store the int value in our orders table. Would this be a good idea? Any thoughts?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
我假设您的数据库设计中已经存在 USER 实体，但是，如果不存在，那么我相信您的解决方案将需要它。
因此，假设 USER 实体存在，正如您所描述的，您可以将 UserID(int) 放在 ORDERS 表上，从而关联所有相关的 USER > ORDER 的详细信息，而不仅仅是缩写（注意：缩写应该不应该存储在 ORDER 表中，而应该存储在 USER 或 < em>USER_DETAILS 表，尽管不是本次讨论的重点）。
然后，您可以将 GUID 列添加到 USER 表中。 我认为不需要单独的查找表。
合理？
I assume that a USER entity already exists in your database design however, if it does not then I believe your solution will require it.
So assuming the USER entity exists, as you have described, you could place the UserID(int) on the ORDERS table, thereby relating all relevant USER details to an ORDER, rather than just initials (Note: Initials should arguably not be stored on the ORDER table but rather the USER or USER_DETAILS table, albeit not the focus of this discussion).
You can then add the GUID column to the USER table. I don’t think a separate lookup table is necessary.
Make sense?
GUID 上的索引性能通常与 16 字节字段上的其他索引没有什么不同。 当您将 GUID 用作 SQL Server 表上的聚集键时，GUID 对性能至关重要。
SQL Server 表是聚集键，并且按该键进行物理排序。 由于 GUID 本质上是完全随机的，这会很快导致大量索引碎片，因此需要 a) 不断的馈送、维护和重组，但 b) 即使您每晚都进行重组，仍然会受到影响。 因此，实际上，最佳实践是避免使用 GUID（甚至是新的“顺序”GUID）作为集群键。
有关为什么 GUID 的聚集键性能非常差的更多背景信息和一些优秀文章，请参阅“索引女王”Kimberly Tripp 的博客：
她洞察力是最有价值的——阅读它、内化它、遵循它！ 你不会后悔的。
马克
Index performance on GUIDs is in general no different from other indexes on a 16-byte field. Where the GUID's are deadly for performance is when you use them as clustering key on a SQL Server table.
The SQL Server table is the clustered key and is physically ordered by that key. Since the GUIDs are by nature totally random, this leads to massive index fragmentation very quickly, and thus requires a) constant feed and care and reorganization, but b) still suffers even if you reorganize every night. So really, the best practice would be to avoid GUIDs (even the new "sequential" GUIDs) for your clustering key.
For more background info and some excellent write-ups on why GUIDs make very poor clustered keys, see the blog of the "Queen of Indexing", Kimberly Tripp:
Her insight is most valuable - read it, internalize it, follow it! You won't regret it.
Marc