Guid是数据库的最佳身份数据类型吗？ [英] Is Guid the best identity datatype for Databases?

问题描述

它连接到BI并且合并来自不同数据源的数据，并将使该过程更加顺利。

It is connected to BI and merging of data from different data sources and would make that process more smooth.

是否存在来自数据库的最佳迁移策略， Guid到没有信息损失的Guid的版本？

And is there an optimal migration strategy from a database without Guids to a version with Guids without information losses?

推荐答案

阅读Frans Bouma的答案后编辑，因为我的答案已经被接受移动到顶部。感谢Frans。

Edited after reading Frans Bouma's answer, since my answer has been accepted and therefore moved to the top. Thanks, Frans.

GUID确实有一个很好的独特价值，但是由于它们的复杂性，它们并不真正是人类可读的，这可以使支持变得困难。如果要使用GUID，您可能需要考虑对批量数据操作进行一些性能分析，然后再进行选择。考虑到如果您的主键是聚集的，则GUID不合适。

GUIDs do make a good unique value, however due to their complex nature they're not really human-readable, which can make support difficult. If you're going to use GUIDs you might want to consider doing some performance analysis on bulk data operations before you make your choice. Take into account that if your primary key is "clustered" then GUIDs are not appropriate.

这是因为聚簇索引导致行在物理上重新排序表插入/更新。由于GUID是随机的，所以每个插入都需要将表中的实际行移动到新行。

This is because a clustered index causes the rows to be physically re-ordered in the table on inserts/updates. Since GUIDs are random, every insert would require actual rows in the table to be moved to make way for the new row.

个人我喜欢有两个键我的数据：

Personally I like to have two "keys" on my data:

1）主键

具有集群主键的唯一的数字值。这是我系统的每个行的内部 ID，用于唯一标识一行和外键。

1) Primary key
Unique, numeric values with a clustered primary key. This is my system's internal ID for each row, and is used to uniquely identify a row and in foreign keys.

身份可能会导致麻烦您正在使用数据库复制（SQL Server将为合并复制表自动添加rowguid列），因为每个服务器实例都会维护身份种子，您将获得重复。

Identity can cause trouble if you're using database replication (SQL Server will add a "rowguid" column automatically for merge-replicated tables) because the identity seed is maintained per server instance, and you'd get duplicates.

2）外部键/外部ID /业务ID

通常还有一个外部ID的附加概念。这通常是一个具有唯一约束的字符字段（可能包括另一列，例如客户标识符）。

2) External Key/External ID/Business ID
Often it is also preferable to have the additional concept of an "external ID". This is often a character field with a unique constraint (possibly including another column e.g. customer identifier).

这将是外部接口使用的值并将暴露给客户（谁不认识你的内部价值观）。这个商业编号可以让客户使用对他们有意义的价值来引用您的数据。

This would be the value used by external interfaces and would be exposed to customers (who do not recognise your internal values). This "business ID" allows customers to refer to your data using values that mean something to them.

这篇关于Guid是数据库的最佳身份数据类型吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！