在 SQLServer2005 环境下 , 表的主键应该怎样设计 .
目前主要用到的主键方案共三种
自动增长主键
手动增长主键
UNIQUEIDENTIFIER 主键
1 、先说自动增长主键 , 它的优点是简单 , 类型支持 bigint. 但是它有致命的弱点 :
当我们需要在多个数据库间进行数据的复制时（ SQL Server 的数据分发、订阅机制允许我们进行库间的数据复制操作），自动增长型字段可能造成数据合并时的主键冲突。设想一个数据库中的 Order 表向另一个库中的 Order 表复制数据库时， OrderID 到底该不该自动增长呢？
2 、再说手动增长主键 , 它的优点是自行定制主键列 , 主键列的数据类型乃至数据样本都可以控制 , 能够稳定的获得目标键值 , 不会重复 . 但是它维护成本比较搞 , 首先生成键值需要自行编写存储过程来产生 , 网络开销大 , 运行时还要考虑到并发冲突等等 .
3 、最后就是 UNIQUEIDENTIFIER 主键 , 它利用 GUID 作为键值 , 可以直接调用 newid() 来获得全局唯一标识 , 即便合并数据表也不会有重复现象 . 但是 UGID 有两个弱点 : 其一 , 和 int 类型比较 ,GUID 长度是前者 4 倍 . 其二 , 用 newid() 获得的 GUID 毫无规律 , 因为该列作为主键 , 必然有聚集索引 , 那么在插入新数据时 , 将是一个非常耗时的操作 . 这样的话 UNIQUEIDENTIFIER 作为主键将大大有损效率 .
所以 SQLServer2000 环境下 DBA 们往往写一个存储过程来生成与时间有关的 GUID, 即在 GUID 前面加上生成时间 . 这样确保生成出来的主键全局唯一并且按时间递增 . 不过这又回到了第二种主键方案 , 不便维护 .
4 、 SQLServer 2005 已经解决了这个问题 , 使用的是 NEWSEQUENTIALID()
这个函数产生的 GUID 是递增的 , 下面看下它的用法
-- 创建实验表
--1 创建 id 列的类型为 UNIQUEIDENTIFIER
--2ROWGUIDCOL 只是这个列的别名 , 一个表中只能有一个
--3PRIMARY KEY 确定 id 为主键
--4 使用 DEFAULT 约束来自动为该列添加 GUID
create table jobs
(
id UNIQUEIDENTIFIER ROWGUIDCOL PRIMARY KEY NOT NULL
CONSTRAINT [DF_jobs_id] DEFAULT ( NEWSEQUENTIALID ()),
account varchar ( 64 ) not null,
password varchar ( 64 ) not null
)
go
select * from jobs
-- 添加实验数据
insert jobs ( account , password ) values ( 'tudou' , '123' )
insert jobs ( account , password ) values ( 'ntudou' , '123' )
insert jobs ( account , password ) values ( 'atudou' , '123' )
insert jobs ( account , password ) values ( 'btudou' , '123' )
insert jobs ( account , password ) values ( 'ctudou' , '123' )
select * from jobs
结果:
-- 使用 identity 的是我们可以通过 Select @@IDENTITY 取到新添加的 id
-- 使用 UNIQUEIDENTIFIER 怎么办呢 ?
-- 采取手动增长的方法 select NEWSEQUENTIALID() 先取出 id 再添加
-- 不行 , 语法不支持
-- 可以通过下面的方法取到新添加数据的 id
-- 在 ADO.NET 中的用法和 Select @@IDENTITY 一样
DECLARE @outputTable TABLE ( ID uniqueidentifier )
INSERT INTO jobs ( account , password )
OUTPUT INSERTED . ID INTO @outputTable
VALUES ( 'dtudou' , '123' )
SELECT ID FROM @outputTable
-- 对比下数据
select * from jobs
结果:
--ROWGUIDCOL 是主键列的别名 , 可以直接当做列名来使用
-- 这样可以忽略主键列的名称
insert jobs ( account , password ) values ( 'etudou' , '123' )
select ROWGUIDCOL from jobs
结果:
