在 SQLServer2005 環(huán)境下 , 表的主鍵應(yīng)該怎樣設(shè)計 .

目前主要用到的主鍵方案共三種

自動增長主鍵

手動增長主鍵

UNIQUEIDENTIFIER 主鍵

1 、先說自動增長主鍵 , 它的優(yōu)點是簡單 , 類型支持 bigint. 但是它有致命的弱點 :

當(dāng)我們需要在多個數(shù)據(jù)庫間進行數(shù)據(jù)的復(fù)制時（ SQL Server 的數(shù)據(jù)分發(fā)、訂閱機制允許我們進行庫間的數(shù)據(jù)復(fù)制操作），自動增長型字段可能造成數(shù)據(jù)合并時的主鍵沖突。設(shè)想一個數(shù)據(jù)庫中的 Order 表向另一個庫中的 Order 表復(fù)制數(shù)據(jù)庫時， OrderID 到底該不該自動增長呢？

2 、再說手動增長主鍵 , 它的優(yōu)點是自行定制主鍵列 , 主鍵列的數(shù)據(jù)類型乃至數(shù)據(jù)樣本都可以控制 , 能夠穩(wěn)定的獲得目標(biāo)鍵值 , 不會重復(fù) . 但是它維護成本比較搞 , 首先生成鍵值需要自行編寫存儲過程來產(chǎn)生 , 網(wǎng)絡(luò)開銷大 , 運行時還要考慮到并發(fā)沖突等等 .

3 、最后就是 UNIQUEIDENTIFIER 主鍵 , 它利用 GUID 作為鍵值 , 可以直接調(diào)用 newid() 來獲得全局唯一標(biāo)識 , 即便合并數(shù)據(jù)表也不會有重復(fù)現(xiàn)象 . 但是 UGID 有兩個弱點 : 其一 , 和 int 類型比較 ,GUID 長度是前者 4 倍 . 其二 , 用 newid() 獲得的 GUID 毫無規(guī)律 , 因為該列作為主鍵 , 必然有聚集索引 , 那么在插入新數(shù)據(jù)時 , 將是一個非常耗時的操作 . 這樣的話 UNIQUEIDENTIFIER 作為主鍵將大大有損效率 .

所以 SQLServer2000 環(huán)境下 DBA 們往往寫一個存儲過程來生成與時間有關(guān)的 GUID, 即在 GUID 前面加上生成時間 . 這樣確保生成出來的主鍵全局唯一并且按時間遞增 . 不過這又回到了第二種主鍵方案 , 不便維護 .

4 、 SQLServer 2005 已經(jīng)解決了這個問題 , 使用的是 NEWSEQUENTIALID()

這個函數(shù)產(chǎn)生的 GUID 是遞增的 , 下面看下它的用法

-- 創(chuàng)建實驗表

--1 創(chuàng)建 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

-- 添加實驗數(shù)據(jù)

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

結(jié)果:

-- 使用 identity 的是我們可以通過 Select @@IDENTITY 取到新添加的 id

-- 使用 UNIQUEIDENTIFIER 怎么辦呢 ?

-- 采取手動增長的方法 select NEWSEQUENTIALID() 先取出 id 再添加

-- 不行 , 語法不支持

-- 可以通過下面的方法取到新添加數(shù)據(jù)的 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

-- 對比下數(shù)據(jù)

select * from jobs

結(jié)果:

--ROWGUIDCOL 是主鍵列的別名 , 可以直接當(dāng)做列名來使用

-- 這樣可以忽略主鍵列的名稱

insert jobs ( account , password ) values ( 'etudou' , '123' )

select ROWGUIDCOL from jobs

結(jié)果:

SQLServer 2005 和自增長主鍵identity說再見——NEWSEQUENTIALID()