SQLServer 2005 和自增长主键identity说再见——NEWSEQUENTIALID()

你的名字 2021-04-06 13:29 532阅读 0赞

\`\`code

在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

\--ROWGUIDCOL是主键列的别名,可以直接当做列名来使用  
\--这样可以忽略主键列的名称

insert jobs (account,password) values ('etudou','123')  
select ROWGUIDCOL from jobs

\--1. 定义临时表变量  
DECLARE @outputTable TABLE(ID uniqueidentifier)  
INSERT INTO TABLE1(col1, col2)  
OUTPUT INSERTED.ID INTO @outputTable  
VALUES('value1', 'value2')  
SELECT ID FROM @outputTable

\--2. 标记ID字段为ROWGUID（一个表只能有一个ROWGUID）  
INSERT INTO TABLE1(col1, col2)  
VALUES('value1', 'value2')  
\--在这里，ROWGUIDCOL其实相当于一个别名  
SELECT ROWGUIDCOL FROM TABLE1

1.  如何设定DEFAULT VALUE为NEWSEQUENTIALID()  
    通过UI的方式设定默认值时，由于SQL SERVER 2005的BUG（即使是SP2也没有解决），导致我们设置了默认值为NEWSEQUENTIALID()保存时会出现以下错误：  
    Warning were encountered during the pre-save validation process, and might result in a failure during save. Do you want to continue attempting to save?  
    'Table1' Table  
    \-Error validating the default for column 'Id'  
    有两种方式可以解决：要么直接点Yes，要么通过CREATE TABLE语句来建表。

\`\`code