Uniqueidentifier与IDENTITY与Material Code——这是主键的最佳选择

Question

在SQL Server中，哪一个是主键的最佳选择
有一些示例代码：

唯一标识符

例如

CREATE TABLE new_employees
   (employeeId   UNIQUEIDENTIFIER      DEFAULT NEWID(),
   fname      VARCHAR(20) )
GO
INSERT INTO new_employees(fname) VALUES ('Karin')
GO

标识列

例如

 CREATE TABLE new_employees
 (
  employeeId int IDENTITY(1,1),
  fname varchar (20)
 );
 INSERT new_employees
    (fname)
 VALUES
    ('Karin');

[物料代码]（或业务代码，即物料的标识，例如客户标识符）

例如

CREATE TABLE new_employees(
    [ClientId] [varchar](20) NOT NULL,
    [fName] [varchar](20) NULL      
 )
 INSERT new_employees
    (ClientID, fname)
 VALUES
    ('C0101000001',--customer identifier，e.g.'C0101000001' a user-defined code.
     'Karin');

请给我一些从三个类型标识列中选择主键的建议，或者其他选择。

谢谢！

Answer 1

GUID似乎是主键的自然选择，如果您真的必须这样做，您可能会争辩将其用于表的primary key。我强烈建议不要这样做使用GUID列作为群集键，SQL Server默认会这样做，除非您特别告诉它不要这样做。

你真的需要把两个问题分开：

1. 主键是一个逻辑构造，它是唯一可靠地标识表中每一行的候选键之一。实际上，这可以是任何东西——INT、GUID、字符串——选择对您的场景最有意义的内容。

2. 集群键（表上定义"集群索引"的一列或多列）-这是一个与物理存储相关的东西，在这里，最好选择一个小的、稳定的、不断增长的数据类型-INT或BIGINT作为默认选项。

默认情况下，SQL Server表上的主键也用作集群键，但不需要这样！我个人看到，当将以前基于GUID的主/集群密钥分解为两个单独的密钥时，性能得到了巨大的提高——GUID上的主（逻辑）密钥和单独的INT IDENTITY(1,1)列上的集群（排序）密钥。

正如Kimberly Tripp（索引女王）和其他人多次指出的那样，GUID作为聚类密钥并不是最优的，因为它的随机性会导致大量的页面和索引碎片，并且通常性能较差。

是的，我知道-SQL Server 2005及更高版本中有newsequentialid()，但即使这样也不是真正的、完全的顺序，因此也会遇到与GUID相同的问题-只是稍微不那么突出

然后还有另一个问题需要考虑：表上的聚类键也将添加到表上每个非聚类索引的每个条目中，因此您确实希望确保它尽可能小。通常，一个有20多亿行的INT应该足以容纳绝大多数表——与GUID作为集群关键字相比，您可以在磁盘和服务器内存中节省数百兆字节的存储空间。

快速计算-使用INT与GUID作为主要和聚类关键字：

• 具有1'000'000行的基本表（3.8 MB与15.26 MB）
• 6个非聚集索引（22.89 MB与91.55 MB）

总计：25 MB对106 MB-这只是一张表！

还有一些思考的食物——金伯利·特里普的精彩作品——读一遍，再读一遍，消化一下！实际上，这是SQL Server索引的福音。

• GUID作为主键和/或聚集键
• 聚集指数争论仍在继续
• 不断增加的聚类关键-聚类指数之争。。。。。。。。。。再次
• 磁盘空间很便宜-这不是重点所在

除非您有一个非常好的理由，否则我认为几乎每个"真实"数据表都应该使用INT IDENTITY作为主键的默认值-它是唯一的、稳定的（永远不会改变）、狭窄的、不断增加的-您希望在集群键中具有的所有好属性都可以快速可靠地实现SQL Server表的性能！

如果您有一些"自然"键值也具有所有这些属性，那么您也可以使用它来代替代理键。但是两个可变长度字符串，每个字符串最多20个字符，在我看来不符合这些要求。

Answer 2

IDENTITY

PROS

1. 存储占地面积小
2. 最佳联接/索引性能（例如，对于时间范围查询，最近插入的大多数行将在有限数量的页面上）
3. 对数据仓库非常有用
4. 操作系统的本地数据类型，并且易于在所有语言中使用
5. 易于调试
6. 自动生成（通过SCOPE_IDENTITY（）检索，而不是分配）
7. 不可更新（尽管有些人认为这是一个缺点，但奇怪的是）

CONS

1. 不能被应用程序可靠地"预测"——只能在INSERT之后检索
2. 在多服务器环境中需要一个复杂的方案，因为在某些形式的复制中不允许使用IDENTITY
3. 如果未明确设置为PRIMARY KEY，则可以重复
4. 如果表上的部分聚集索引，则可以创建一个插入热点
5. 专有且不可直接移植
6. 仅在单个表中唯一
7. 可能会出现间隙（例如，回滚事务），这可能会导致chicken-little风格的警报

GUID

PROS

1. 由于它们{或多或少}保证是唯一的，多个表/数据库/实例/服务器/网络/数据中心可以独立生成它们，然后在没有冲突的情况下进行合并；

2. 某些复制形式所需的；

3. 可以在数据库之外生成（例如由应用程序生成）
4. 分布式值可以防止热点（只要您不对此列进行集群，这可能会导致异常高的碎片）

CONS

1. 更宽的数据类型会导致索引性能下降（如果是聚集的，则每次插入几乎保证会"弄脏"不同的页面），并增加存储需求
2. 调试繁琐（其中userid={BAE7DF4-DDF-3RG-5TY3E3RF456AS10}）
3. 可更新（需要提议更改，或完全阻止活动）
4. 对某些环境中的时间回滚敏感（例如夏令时回滚）
5. GROUP BY和其他设置操作通常需要CAST/CONVERT
6. 并非所有语言和环境都直接支持GUID
7. 没有像SCOPE_GUID（）这样的语句来确定生成的值，例如由NEWID（）生成的值

Answer 3

在设计表时需要考虑的一件事是，是否需要将数据从一个地方复制、分片或以其他方式移动到另一个地方。也许数据是由其他应用程序生成的，需要与您的应用程序保持同步。例如，一个移动应用程序可以创建数据，然后将其与服务器同步。如果类似的情况是或可能是真的，那么UNIQUEIDENTIFIER将是用于主键的好选择。

当用作聚集索引时，UNIQUEIDENTIFIER数据类型的性能非常糟糕。是的，您可以使用newsequentialid()，但如果这些值是在其他设备上生成的，则这对您没有帮助。人们一致认为，聚集索引最好与顺序和窄数据类型（如INT或BIGINT）一起使用。

如果您不关心存储空间问题，则可以尝试同时使用IDENTITY集群密钥和UNIQUEIDENTIFIER主键。创建集群键IDENTITY列，并将其用于集群索引（但不是主键）。插入仍将按顺序进行，并且它满足了窄数据类型的要求。现在，您可以使用UNIQUEIDENTIFIER作为主键。这将允许您在需要时移动、复制和/或分割数据。

集群键除了保持插入的顺序性，以及在查找给定查询的数据时成为所有其他非集群索引所指向的索引之外，没有其他用途。集群密钥是完全丢弃的，并且可以在移动、复制和/或分片数据时重新生成，因为唯一性由UNIQUEIDENTIFIER主键处理。

这是一篇很好的文章，演示了在为聚集索引使用IDENTITY和UNIQUEIDENTIFIER时内部会发生什么。

• 有效聚集索引

Answer 4

GUID很大，但其优点是在任何地方都是唯一的：这个表或那个表，这个服务器或那个服务器，如果您有GUID，那么其他一切都是已知的。如果这对你有用，那就太好了，但你会用间接费用来支付，并继续支付，支付，支付。。。。

材质代码只适用于较小的不可变键，如颜色或分类代码等。R将始终是红色，G将是绿色，它是一个字节，等等

当可能没有材料代码，或者自然键由几个材料代码组合在一起，或者自然密钥已经由其他标识列和/或GUID组成，或者自然关键字是可变的时，标识列就会形成自己的标识列。是的，您可以使用GUID，但整数列在所有方面都要高效得多。

SQL 2012中提供的另一个选项是序列，有点像数据库级别的标识列。这是GUID和标识列之间的一个很好的中间点，因为序列可以在许多表中使用，因此从给定的值来看，不仅行是已知的，表也是已知的——但如果您认为这对您的数据来说足够的话，您仍然可以使用INT或BIGINT（或SMALLINT！）。对于某些目的来说，这有点漂亮，有点像OO世界中的对象id。

请注意，许多或轻量级ORM希望表具有单列主键，最好是整数列，并且可能无法很好地处理INT IDENTITY PK以外的任何内容。