Uniqueidentifier vs. IDENTITY vs. Material Code——哪个是主键的最佳选择? [英] Uniqueidentifier vs. IDENTITY vs. Material Code --which is the best choice for primary key?

问题描述

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

Which one is the best choice for primary key in SQL Server?
There are some example code:

唯一标识符

例如

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');

请给我一些建议，从三种类型标识列中选择主键，或其他选择.

Please give me some advices for choosing the primary key from the three type identity columns,or other choices.

谢谢！

推荐答案

GUID 似乎是您的主键的自然选择 - 如果您真的必须，您可能会争论使用它对于表的 PRIMARY KEY.我强烈建议不要做是使用 GUID 列作为 集群键，SQL Server 默认情况下这样做，除非你特别告诉它不要.

GUID may seem to be a natural choice for your primary key - and if you really must, you could probably argue to use it for the PRIMARY KEY of the table. What I'd strongly recommend not to do is use the GUID column as the clustering key, which SQL Server does by default, unless you specifically tell it not to.

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

You really need to keep two issues apart:

1. 主键 是一种逻辑结构 - 唯一且可靠地标识表中每一行的候选键之一.这可以是任何东西，实际上 - INT、GUID、字符串 - 选择最适合您的场景的内容.

1. the primary key is a logical construct - one of the candidate keys that uniquely and reliably identifies every row in your table. This can be anything, really - an INT, a GUID, a string - pick what makes most sense for your scenario.

聚簇键(定义表上聚簇索引"的一列或多列)——这是一个物理存储相关的东西，并且在这里，一个小的、稳定的、不断增加的数据类型是你最好的选择 - INT 或 BIGINT 作为你的默认选项.

the clustering key (the column or columns that define the "clustered index" on the table) - this is a physical storage-related thing, and here, a small, stable, ever-increasing data type is your best pick - INT or BIGINT as your default option.

默认情况下，SQL Server 表上的主键也用作集群键 - 但不必如此！当将以前的基于 GUID 的主键/集群键分解为两个单独的键时，我个人看到了巨大的性能提升 - GUID 上的主(逻辑)键和上的集群(排序)键一个单独的 INT IDENTITY(1,1) 列.

By default, the primary key on a SQL Server table is also used as the clustering key - but that doesn't need to be that way! I've personally seen massive performance gains when breaking up the previous GUID-based primary / clustered key into two separate keys - the primary (logical) key on the GUID, and the clustering (ordering) key on a separate INT IDENTITY(1,1) column.

作为 KimberlyTripp - 索引女王 - 和其他人已经说过很多次 - GUID 作为聚类键不是最佳的，因为由于其随机性，这将导致大量的页面和索引碎片，并且通常会导致性能不佳.

As Kimberly Tripp - the Queen of Indexing - and others have stated a great many times - a GUID as the clustering key isn't optimal, since due to its randomness, it will lead to massive page and index fragmentation and to generally bad performance.

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

Yes, I know - there's newsequentialid() in SQL Server 2005 and up - but even that is not truly and fully sequential and thus also suffers from the same problems as the GUID - just a bit less prominently so.

然后还有另一个问题需要考虑:表上的聚簇键也将添加到表上每个非聚簇索引的每个条目中 - 因此您真的想确保它尽可能小.通常，具有 2+ 十亿行的 INT 应该足以满足绝大多数表 - 与作为集群键的 GUID 相比，您可以为自己节省数百兆字节磁盘和服务器内存中的存储量.

Then there's another issue to consider: the clustering key on a table will be added to each and every entry on each and every non-clustered index on your table as well - thus you really want to make sure it's as small as possible. Typically, an INT with 2+ billion rows should be sufficient for the vast majority of tables - and compared to a GUID as the clustering key, you can save yourself hundreds of megabytes of storage on disk and in server memory.

快速计算 - 使用 INT 与 GUID 作为主键和聚类键:

Quick calculation - using INT vs. GUID as primary and clustering key:

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

总计:25 MB 对 106 MB - 这只是在一张桌子上！

TOTAL: 25 MB vs. 106 MB - and that's just on a single table!

一些值得深思的食物 - 金伯利·特里普 (Kimberly Tripp) 的优秀作品 - 阅读、再阅读、消化它！这是 SQL Server 索引的福音，真的.

Some more food for thought - excellent stuff by Kimberly Tripp - read it, read it again, digest it! It's the SQL Server indexing gospel, really.

• GUID 作为PRIMARY KEY 和/或聚集键
• 聚集索引的争论还在继续
• 曾经- 增加聚集键 - 聚集索引辩论.......... 再次！
• 磁盘空间很便宜 - 不是 重点！

除非您有非常充分的理由，否则我认为几乎每个真实"数据表都使用 INT IDENTITY 作为其主键的默认值 - 这是独特、稳定(永不改变)、狭窄、不断增加 - 您希望在集群键中拥有所有良好属性，以实现 SQL Server 表的快速可靠性能！

Unless you have a very good reason, I would argue to use a INT IDENTITY for almost every "real" data table as the default for their primary key - it's unique, it's stable (never changes), it's narrow, it's ever increasing - all the good properties that you want to have in a clustering key for fast and reliable performance of your SQL Server tables!

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

If you have some "natural" key value that also has all those properties, then you might also use that instead of a surrogate key. But two variable-length strings of max. 20 chars each do not meet those requirements in my opinion.

这篇关于Uniqueidentifier vs. IDENTITY vs. Material Code——哪个是主键的最佳选择?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！