IDENTITY資料欄位取號模式

IDENTITY資料欄位取號模式

 

我們在設計資料表時,大家常會設計一個流水號欄位來當PK或是CLUSTERED INDEX使用。而最常用的方式就是將該欄位設定為IDENTITY,讓SQL根據我們的需求(識別值增量)自動取號。

 

clip_image002

情境一 : 某日在做資料表匯出的ETL作業時突然想到如果兩張一樣資料格式的資料表T1及資料表T2。如果資料表T1目前取號是到1000,而資料表T2是到3000。而我們如果將資料表T2中的編號2000-30001000筆資料寫入到資料表T1(寫入過程要啟用識別插入),完成寫入後資料表T1就有1-10002001-3000的這兩個編號區塊的資料,如果此時再寫入一筆資料到資料表T1SQL的給號是1001還是3001?來做個小實驗吧。

SET NOCOUNT ON;
/*建立資料表T1及T2,兩張資料表SCHEMA都一樣有一IDENTITY的欄位*/
CREATE TABLE T1(ID INT IDENTITY,NAME CHAR(10));
CREATE TABLE T2(ID INT IDENTITY,NAME CHAR(10));
GO
/*在T1寫入1000筆資料*/
INSERT INTO T1(NAME) VALUES('ROCK');
GO 1000
/*在T2寫入3000筆資料*/
INSERT INTO T2(NAME) VALUES('ROCK');
GO 3000
/*將T2前2000筆資料刪除*/
DELETE T2 WHERE ID < 2001;
GO
/*顯示T1目前的IDENTITY VALUE*/
DBCC CHECKIDENT('T1',NORESEED);
GO
/*啟用識別插入,將T2的資料寫入到T1*/
SET IDENTITY_INSERT T1 ON;
INSERT INTO T1(ID,NAME) SELECT ID,NAME FROM T2;
SET IDENTITY_INSERT T1 OFF;
GO
/*完成T2資料表寫入到T1後,我們在看一下目前的IDENTITY VALUE*/
DBCC CHECKIDENT('T1',NORESEED);
GO
/*再寫入一筆資料到T1*/
INSERT INTO T1(NAME) VALUES('ROCK');
GO
/*我們在看一下目前的IDENTITY VALUE*/
DBCC CHECKIDENT('T1',NORESEED);
GO
SET NOCOUNT OFF;

 

執行完上述語法後我們可以看見SQL輸出的訊息(如下圖)

1.      我們在資料表T1寫入1000筆資料後DBCC CHECKIDENT。識別值是1000,資料行值是1000

2.      我們將資料表T2編號2001-30001000筆資料透過啟用識別插入寫入到T1後,T1的識別值為3000而資料行值也是3000

3.      完成上述兩個動作後,我們此時INSERT一筆資料進T1T1資料表會如何編號呢? 如下圖所示T1的識別值為3001而資料行值也是3001。因此我們可以知道T1資料表跳過1001-20001000個號碼,改由3001開始編號。

clip_image004

 

 

情境二 : 資料表T1寫入3000筆資料,而資料表T2是寫入1000筆。而我們如果將資料表T1中的3000筆資料刪除,然後將資料表T2編號1-10001000筆資料寫入到資料表T1(寫入過程要啟用識別插入),完成寫入後,資料表T1就有1-1000編號區塊的資料,如果此時再寫入一筆資料到資料表T1SQL的給號是1001還是3001?

SET NOCOUNT ON;
/*建立資料表T1及T2,兩張資料表SCHEMA都一樣有一IDENTITY的欄位*/
CREATE TABLE T1(ID INT IDENTITY,NAME CHAR(10));
CREATE TABLE T2(ID INT IDENTITY,NAME CHAR(10));
GO
/*在T1寫入3000筆資料*/
INSERT INTO T1(NAME) VALUES('ROCK');
GO 3000
/*在T2寫入1000筆資料*/
INSERT INTO T2(NAME) VALUES('ROCK');
GO 1000
/*刪除T1資料表所有資料*/
DELETE T1;
GO
/*顯示T1目前的IDENTITY VALUE*/
DBCC CHECKIDENT('T1',NORESEED);
GO
/*啟用識別插入,將T2的資料寫入到T1*/
SET IDENTITY_INSERT T1 ON;
INSERT INTO T1(ID,NAME) SELECT ID,NAME FROM T2;
SET IDENTITY_INSERT T1 OFF;
GO
/*完成T2資料表寫入到T1後,我們在看一下目前的IDENTITY VALUE*/
DBCC CHECKIDENT('T1',NORESEED);
GO
/*再寫入一筆資料到T1*/
INSERT INTO T1(NAME) VALUES('ROCK');
GO
/*我們在看一下目前的IDENTITY VALUE*/
DBCC CHECKIDENT('T1',NORESEED);
GO
SET NOCOUNT OFF;

 

 

執行完上述語法後我們可以看見SQL輸出的訊息(如下圖)

1.      我們在資料表T1寫入3000筆資料後再全數刪除做DBCC CHECKIDENTT1識別值是3000,資料行值是NULL

2.      我們將資料表T2編號1-10001000筆資料透過啟用識別插入寫入到T1後,T1的識別值為3000而資料行值是1000

3.      完成上述兩個動作後,我們此時INSERT一筆資料進T1T1資料表會如何編號呢?如下圖所示T1的識別值為3001而資料行值也是3001。因此我們可以知道T1資料表跳過1001-30002000個號碼,改由3001開始編號。

clip_image006

 

 

結論 : 經過上面實驗,我們知道資料表會記錄IDENTITY欄位識別值,然後根據該識別值增量繼續取號。而不是依據目前該欄位的資料行值。那萬一如 [情境二] 的情況,我們想讓識別值等於資料行值,那該怎麼做呢?其實就是利用DBCC CHECKIDENT(Table_Name, RESEED, New_Reseed_Value)
 

當我們想將資料表IDENTITY欄位的識別值設為1000,而讓下一筆資料從1001開始編號,則我們可以用DBCC CHECKIDENT('T1',RESEED,1000)來重新設定識別值。

 

當我們TRUNCATE TABLE後,該資料表IDENTITY欄位的識別值會歸零,如果資料表因為有FK導致無法使用TRUNCATE TABLE來刪除資料(需用DELETE刪除)但又想歸零IDENTITY欄位的識別值,那就使用剛剛介紹的方式嘍DBCC CHECKIDENT('T1',RESEED,0)來歸零識別值。

 

參考 : DBCC CHECKIDENT

我是ROCK

rockchang@mails.fju.edu.tw