Rock的SQL筆記本

 

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

 

情境一 : 某日在做資料表匯出的ETL作業時突然想到如果兩張一樣資料格式的資料表T1及資料表T2。如果資料表T1目前取號是到1000，而資料表T2是到3000。而我們如果將資料表T2中的編號2000-3000這1000筆資料寫入到資料表T1(寫入過程要啟用識別插入)，完成寫入後資料表T1就有1-1000及2001-3000的這兩個編號區塊的資料，如果此時再寫入一筆資料到資料表T1，SQL的給號是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-3000的1000筆資料透過啟用識別插入寫入到T1後，T1的識別值為3000而資料行值也是3000。

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

 

 

情境二 : 資料表T1寫入3000筆資料，而資料表T2是寫入1000筆。而我們如果將資料表T1中的3000筆資料刪除，然後將資料表T2編號1-1000這1000筆資料寫入到資料表T1(寫入過程要啟用識別插入)，完成寫入後，資料表T1就有1-1000編號區塊的資料，如果此時再寫入一筆資料到資料表T1，SQL的給號是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 CHECKIDENT。T1識別值是3000，資料行值是NULL。

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

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

 

 

結論 : 經過上面實驗，我們知道資料表會記錄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