積沙成塔

【情境說明】

在論壇上有朋友討論到 SQL Database 不支援使用 DBCC CHECKIDENT 來重設識別值，是否有其他替代方案？在 SQL Azure 官方論壇上看到有國外的朋友提出變通的作法以及小朱版主提到砍掉資料表重建的方式有異曲同工之妙，筆者將之實作步驟整理如下，在此跟大家分享。

【前置作業】

首先筆者在 SQL Database 管理入口網站利用網站提供的設計工具來建立一個測試資料表（Table1），包含 ID、c1 和 c2 等三個欄位，其中 ID 為 identity。

 

 

接著點選【New Query】，接著輸入下列的 T-SQL 後按【Run】來新增測試資料。

 

   1: INSERT INTO Table1(c1,c2) VALUES ('a','aa'),('b','bb'),('c','cc')

 

執行結果如下：

 

 

若您嘗試在 SQL Database 上執行 DBCC CHECKIDENT 指令，將會發生【DBCC command 'CHECKIDENT' is not supported in this version of SQL Server.】的錯誤訊息。

 

 

【解決方式】

假設您利用 T-SQL 刪除 ID 大於 1 的資料只留一筆資料，若後續再利用 INSERT 敘述來新增資料時，依照識別欄位的特性，除非利用 DBCC CHECKIDENT 來重設識別值，否則 ID 會從 4 開始累加。但由於 SQL Database 不支援 DBCC CHECKIDENT，若您想要讓識別值從 2 開始，您可以利用下列T-SQL 來達到相同目的：

   1: --故意刪除ID大於1的資料

   2: DELETE Table1 WHERE id > 1

   3: GO

   4:  

   5:  

   6: --STEP 1、建立臨時資料表

   7: IF OBJECT_ID('dbo.Table1_temp') IS NOT NULL

   8:     DROP TABLE Table1_temp

   9: GO

  10:  

  11: CREATE TABLE Table1_temp (id int identity primary key,c1 nvarchar(50),c2 nvarchar(15))

  12:  

  13: --STEP 2、允許將明確的值插入資料表的識別欄位中

  14: SET  IDENTITY_INSERT TestDB.dbo.Table1_temp ON

  15:  

  16: --STEP 3、插入資料到臨時資料表

  17: INSERT INTO Table1_temp(ID,c1,c2)

  18: SELECT * FROM Table1

  19:  

  20: --STEP 4、關閉允許將明確的值插入資料表的識別欄位中

  21: SET  IDENTITY_INSERT TestDB.dbo.Table1_temp OFF

  22: GO

  23:  

  24: --STEP 5、刪除原始資料表

  25: DROP TABLE Table1

  26:  

  27: --STEP 6、將臨時資料表改為原始資料表

  28: EXEC dbo.sp_rename @objname = N'[dbo].[Table1_temp]', @newname = N'Table1', @objtype = N'OBJECT'

  29:  

  30: --STEP 7、插入資料

  31: INSERT INTO Table1(c1,c2) VALUES ('b','bb'),('c','cc')

  32:  

  33: SELECT * 

  34: FROM Table1

 

實際畫面如下：

 

 

當執行完畢之後您將看到新增的資料可以從 ID 等於 2 開始插入，而不是從 4 開始，用到的概念是先建立一個和原始資料表（Table1）結構相同的臨時表，接著利用 SET IDENTITY_INSERT ON 選向來開啟可以插入識別欄位的值，就可以達到類似重新設定識別值的效果（執行結果如下）。

 

 

【參考資料】

- DBCC CHECKIDENT (Transact-SQL)

- SQL AZURE - Identity Reseed