[SQL] 移除 identity

遇上需要把 identity 移除情境，在 ALTER TABLE ddl 語法內竟然找不到相關語法，使用 [SSMS] 產生變更指令碼 確認 ddl 語法，發現竟然是跑該流程

1. 建立暫存 table
2. 把資料 insert 進暫存 table
3. 刪除原 table
4. 把暫存 table 更名為原 table 名稱
5. 建立叢集索引

產生變更指令碼如下

use AdventureWorks2022
go 

/* 為了避免任何可能發生資料遺失的問題，您應該先詳細檢視此指令碼，然後才能在資料庫設計工具環境以外的位置執行。*/
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
CREATE TABLE dbo.Tmp_tblRemoveIdentity
	(
	ID int NOT NULL
	)  ON [PRIMARY]
GO
ALTER TABLE dbo.Tmp_tblRemoveIdentity SET (LOCK_ESCALATION = TABLE)
GO
IF EXISTS(SELECT * FROM dbo.tblRemoveIdentity)
	 EXEC('INSERT INTO dbo.Tmp_tblRemoveIdentity (ID)
		SELECT ID FROM dbo.tblRemoveIdentity WITH (HOLDLOCK TABLOCKX)')
GO
DROP TABLE dbo.tblRemoveIdentity
GO
EXECUTE sp_rename N'dbo.Tmp_tblRemoveIdentity', N'tblRemoveIdentity', 'OBJECT' 
GO
ALTER TABLE dbo.tblRemoveIdentity ADD CONSTRAINT
	PK_tblRemoveIdentity PRIMARY KEY CLUSTERED 
	(
	ID
	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO
COMMIT

Google 後也發現都是採取該流程來移除 identity 

• 參考資料
• Remove Identity from a column in a table
• SQL SERVER – Add or Remove Identity Property on Column
• Remove the IDENTITY property from an existing column