《简谈SQLServer主键和自动编号问题》由会员分享，可在线阅读，更多相关《简谈SQLServer主键和自动编号问题（8页珍藏版）》请在金锄头文库上搜索。
1、简谈简谈 SQLSQL ServerServer 主键和自动编号问题主键和自动编号问题作者：51cto 出处：IT 专家网论坛 2009-05-16 07:00所谓主键就是能够唯一标识表中某一行的属性或属性组，一个表只能有一个主 键，但可以有多个候选索引。因为主键可以唯一标识某一行记录，所以可以确 保执行数据更新、删除的时候不会出现张冠李戴的错误。所谓主键就是能够唯一标识表中某一行的属性或属性组，一个表只能有一 个主键，但可以有多个候选索引。因为主键可以唯一标识某一行记录，所以可 以确保执行数据更新、删除的时候不会出现张冠李戴的错误。当然，其它字段可以辅助我们在执行这些操作时消除共享冲突，不过
2、就不 在这里讨论了。主键除了上述作用外，常常与外键构成参照完整性约束，防止 出现数据不一致。所以数据库在设计时，主键起到了很重要的作用。常见的数据库主键选取方式有：自动增长字段手动增长字段UniqueIdentifier“COMB(Combine)”类型一、自动增长型字段一、自动增长型字段很多数据库设计者喜欢使用自动增长型字段，因为它使用简单。自动增长 型字段允许我们在向数据库添加数据时，不考虑主键的取值，记录插入后，数 据 库系统会自动为其分配一个值，确保绝对不会出现重复。如果使用 SQL Server 数据库的话，我们还可以在记录插入后使用IDENTITY 全局变量获取系 统分配的主键键值
3、。尽管自动增长型字段会省掉我们很多繁琐的工作，但使用它也存在潜在的 问题，那就是在数据缓冲模式下，很难预先填写主键与外键的值。假设有两张 表：Order(OrderID, OrderDate)OrderDetial(OrderID, LineNum, ProductID, Price)Order 表中的 OrderID 是自动增长型的字段。现在需要我们录入一张订单， 包括在 Order 表中插入一条记录以及在 OrderDetail 表中插入若干条记 录。因 为 Order 表中的 OrderID 是自动增长型的字段，那么我们在记录正式插入到数据库之前无法事先得知它的取值，只有在更新后才能知道
4、数据库为它 分配的是 什么值。这会造成以下矛盾发生：首先，为了能在 OrderDetail 的 OrderID 字段中添入正确的值，必须先更 新 Order 表以获取到系统为其分配的 OrderID 值，然后再用这个 OrderID 填充 OrderDetail 表。最后更新 OderDetail 表。但是，为了确保数据的一致性， Order 与 OrderDetail 在更新 时必须在事务保护下同时进行，即确保两表同时 更行成功。听棠.NET 指出：主档放在事务中提交时，通过IDENTITY 就可以取到生 成值的，因此可以传给明细当外键用，而且在事务发生错误回滚时，主档记录 也会被回滚取消的
5、。吕震宇补充：使用自动增长字段会增加网络的 roundTrip。尽管可以使用 IDENTITY 取得主键的值，但在更新过程中，不得不增加一次数据往返(以 C/S 结构为例)：1、客户端发送开始事务命令2、客户端提交主表更新3、服务器返回IDENTITY4、客户端根据返回的主键更新从表缓冲5、客户端将从表提交服务器更新6、客户端提交事务在这里多了一次往返就会增加了事务处理的时间。降低并发性能。如果不用自动增长型字段，将是以下情景：1、客户端发送开始事务命令2、客户端提交主表更新3、客户端提交从表更新4、客户端提交事务因此我不赞成使用自动增长型字段作为主键与外键链接的纽带。除此之外，当我们需要在多
6、个数据库间进行数据的复制时(SQL Server 的 数据分发、订阅机制允许我们进行库间的数据复制操作)，自动增长型字段可能造成数据合并时的主键冲突。设想一个数据库中的 Order 表向另 一个库中的 Order 表复制数据库时，OrderID 到底该不该自动增长呢?ADO.NET 允许我们在 DataSet 中将某一个字段设置为自动增长型字段，但 千万记住，这个自动增长字段仅仅是个占位符而已，当数据库进行更新时，数 据库生成的值会自动取代 ADO.NET 分配的值。所以为了防止用户产生误解，建 议大家将 ADO.NET 中的自动增长初始值以及增量都设置成-1。此外，在 ADO.NET 中，我
7、们 可以为两张表建立 DataRelation，这样存在级联关系的两张 表更新时，一张表更新后另外一张表对应键的值也会自动发生变化，这会大大 减少了我们对 存在级联关系的两表间更新时自动增长型字段带来的麻烦。二、手动增长型字段二、手动增长型字段既然自动增长型字段会带来如此的麻烦，我们不妨考虑使用手动增长型的 字段，也就是说主键的值需要自己维护，通常情况下需要建立一张单独的表存 储 当前主键键值。还用上面的例子来说，这次我们新建一张表叫 IntKey，包 含两个字段，KeyName 以及 KeyValue。就像一个 HashTable，给一个 KeyName，就可以知道目前的 KeyValue
8、是什么，然后手工实现键值数据递增。 在 SQL Server 中可以编写这样一个存储过程，让取键值的过程自动进行。代码 如下：CREATE PROCEDURE GetKeyKeyName char(10), KeyValue int OUTPUT AS UPDATE IntKey SET KeyValue = KeyValue = KeyValue + 1 WHERE Ke yName = KeyName GO这样，通过调用存储过程，我们可以获得最新键值，确保不会出现重复。 若将 OrderID 字段设置为手动增长型字段，我们的程序可以由以下几步来 实现： 首先调用存储过程，获得一个 Orde
9、rID，然后使用这个 OrderID 填充 Order 表 与 OrderDetail 表，最后在事务保护下对两表进行 更新。使用手动增长型字段作为主键在进行数据库间数据复制时，可以确保数据 合并过程中不会出现键值冲突，只要我们为不同的数据库分配不同的主键取值 段 就行了。但是，使用手动增长型字段会增加网络的 RoundTrip，我们必须通 过增加一次数据库访问来获取当前主键键值，这会增加网络和数据库的负载， 当 处于一个低速或断开的网络环境中时，这种做法会有很大的弊端。同时，手 工维护主键还要考虑并发冲突等种种因素，这更会增加系统的复杂程度。三、使用三、使用 UniqueIdentifier
10、UniqueIdentifierSQL Server 为我们提供了 UniqueIdentifier 数据类型，并提供了一个生 成函数 NEWID( )，使用 NEWID( )可以生成一个唯一的 UniqueIdentifier。UniqueIdentifier 在数据库中占用 16 个字节，出现重复 的概率非常小，以至于可以 认为是 0。我们经常从注册表中看到类似 45F0EB02-0727-4F2E-AAB5-E8AEDEE0CEC5的东西实际上就是一个 UniqueIdentifier，Windows 用它来做 COM 组件以及接口的标识，防止出现重 复。在.NET 里管 UniqueI
11、dentifier 称 之为 GUID(Global Unique Identifier)。 在 C#中可以使用如下命令生成一个 GUID：Guid u = System.Guid.NewGuid();对于上面提到的 Order 与 OrderDetail 的程序，如果选用 UniqueIdentifier 作为主键的话，我们完全可以避免上面提到的增加网络 RoundTrip 的问题。通过程序直接生成 GUID 填充主键，不用考虑是否会出现重 复。UniqueIdentifier 字段也存在严重的缺陷：首先，它的长度是 16 字节， 是整数的 4 倍长，会占用大量存储空间。更为严重的是，Uni
12、queIdentifier 的 生成毫无规律 可言，要想在上面建立索引(绝大多数数据库在主键上都有索引) 是一个非常耗时的操作。有人做过实验，插入同样的数据量，使用 UniqueIdentifier 型数据做主键要比使用 Integer 型数据慢，所以，出于效率 考虑，尽可能避免使用 UniqueIdentifier 型 数据库作为主键键值。四、使用四、使用“COMB(Combine)”“COMB(Combine)”类型类型既然上面三种主键类型选取策略都存在各自的缺点，那么到底有没有好的 办法加以解决呢?答案是肯定的。通过使用 COMB 类型(数据库中没有 COMB 类型， 它是 Jimmy
13、Nilsson 在他的“The Cost of GUIDs as Primary Keys”一文中设 计出来的)，可以在三者之间找到一个很好的平衡点。COMB 数据类型的基本设计思路是这样的：既然 UniqueIdentifier 数据因 毫无规律可言造成索引效率低下，影响了系统的性能，那么我们能不能通过组 合 的方式，保留 UniqueIdentifier 的前 10 个字节，用后 6 个字节表示 GUID 生成的时间(DateTime)，这样我们将时间信息与 UniqueIdentifier 组合起来， 在保留 UniqueIdentifier 的唯一性的同时增加了有序性，以此来提高索引效
14、率。 也许有人会担心 UniqueIdentifier 减少到 10 字节会造成数据出现重复，其实 不用担心，后 6 字节的时间精度可以达到 1/300 秒，两个 COMB 类型数据完全 相同的可能性是在这 1/300 秒内生成的两个 GUID 前 10 个字节完全相同，这几 乎是不可能的!在 SQL Server 中用 SQL 命令将这一思路实现出来便是：DECLARE aGuid UNIQUEIDENTIFIERSET aGuid = CAST(CAST(NEWID() AS BINARY(10) + CAST(GETDATE() AS BINARY(6) AS UNIQUEIDENTIF
15、IER)经过测试，使用 COMB 做主键比使用 INT 做主键，在检索、插入、更新、删 除等操作上仍然显慢，但比 Unidentifier 类型要快上一些。关于测试数据可以 参考我 2004 年 7 月 21 日的随笔。除了使用存储过程实现 COMB 数据外，我们也可以使用 C#生成 COMB 数据， 这样所有主键生成工作可以在客户端完成。C#代码如下：/= =/*/ / 返回 GUID 用于数据库操作，特定的时间代码可以提高检索效率/ / COMB (GUID 与时间混合型) 类型 GUID 数据 public static Guid NewComb() byte guidArray = S
16、ystem.Guid.NewGuid().ToByteArray();DateTime baseDate = new DateTime(1900,1,1);DateTime now = DateTime.Now; / Get the days and milliseconds which will be used to build the byte string TimeSpan days = new TimeSpan(now.Ticks -baseDate.Ticks);TimeSpan msecs = new TimeSpan(now.Ticks -(new DateTime(now.Year, now.Month, now.Day).Ticks); / Convert to a byte array / Note that SQL Server is acc
