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

问题描述

哪个是SQL Server中主键的最佳选择？

有一些示例代码：

Uniqueidentifiers

例如

  CREATE TABLE new_employees 
 UNIQUEIDENTIFIER DEFAULT NEWID（），
 fname VARCHAR（20））
 GO 
 INSERT INTO MyUniqueTable（Characters）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 
（fname）
 VALUES 
（'C0101000001'， - 客户标识符，例如'C0101000001'用户定义的代码
'Karin'）; 
  

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

谢谢！

解决方案

GUID 可能似乎是你的主键的自然选择 - 如果你真的必须，你可能会争论使用它的表的PRIMARY KEY。我强烈建议不要使用 GUID 列作为SQL Server所需的群集键

您真的需要将两个问题分开：

1. 主键是一个逻辑构造 - 一个候选键，可以唯一可靠地标识表格中的每一行。这可以是任何东西，真的 - INT ， GUID ，一个字符串 - 选择什么对你的场景最有意义

（ em> 存储相关的事情，这里，一个小的，稳定的，不断增加的数据类型是你最好的选择 - INT 或 BIGINT 作为默认选项。

默认情况下，SQL Server表上的主键也用作集群键， t需要这样！我将以前的基于GUID的主/集群密钥分解成两个单独的密钥 - GUID 上的主（逻辑）密钥，以及聚类（排序）键放在单独的 INT IDENTITY（1,1）列上。

如 Kimberly Tripp - 索引之女王 - 和其他人已经说了很多次 - GUID 作为聚类键不是最佳的，因为由于它的随机性，它将导致大量的页面和索引碎片和一般性能不佳。

是，我知道 - 在SQL Server 2005和更高版本中有 newsequentialid（） - 但即使这不是真正和完全顺序的，因此也遭受与<$ cc相同的问题$ c> GUID - 稍微不太显眼。

然后还有一个问题需要考虑：到表上每个非聚集索引的每个条目 - 所以你真的想确保它尽可能小。通常，具有2亿个行的 INT 应该足够用于绝大多数表 - 并且与 GUID 作为集群密钥，您可以在磁盘和服务器内存中保存数百MB的存储。

快速计算 - 使用 INT 与 GUID 主要和群集键：

• 基本表格，包含1'000'000行（3.8 MB对比15.26 MB）


• 6个非聚集索引（22.89 MB vs. 91.55 MB）

TOTAL：

更多的食物 - Kimberly Tripp的优秀作品 - 阅读它，再次阅读，消化它！这是SQL Server索引的福音，真的。

• GUIDs as PRIMARY KEY and / or clustered key


• 聚集索引争论继续


• 不断增加的聚类键 - 聚集索引辩论..........


• 磁盘空间便宜 - 这不是 li>
  

除非您有很好的理由，否则我会争辩使用 INT IDENTITY 几乎每个真实数据表作为其主键的默认值 - 它是唯一的，它是稳定的（从不改变），它狭窄，它不断增加 - 所有的良好属性

如果你有一些自然的键值，也有所有的属性，那么您也可以使用它而不是代理键。但是两个可变长度字符串max。 20个字符每个都不符合我的意见中的那些要求。

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

Uniqueidentifiers

e.g.

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

Identity columns

e.g.

 CREATE TABLE new_employees
 (
  employeeId int IDENTITY(1,1),
  fname varchar (20)
 );

 INSERT new_employees
    (fname)
 VALUES
    ('Karin');

[Material Code](or Business Code,which identity of a material. e.g. customer identifier)

e.g.

CREATE TABLE new_employees(
    [ClientId] [varchar](20) NOT NULL,
    [fName] [varchar](20) NULL      
 )

 INSERT new_employees
    (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.

Thanks!

解决方案

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. 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.

2. 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.

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.

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.

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.

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.

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

• Base Table with 1'000'000 rows (3.8 MB vs. 15.26 MB)
• 6 nonclustered indexes (22.89 MB vs. 91.55 MB)

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

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.

• GUIDs as PRIMARY KEY and/or clustered key
• The clustered index debate continues
• Ever-increasing clustering key - the Clustered Index Debate..........again!
• Disk space is cheap - that's not the point!

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!

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屋！