SQL Server 刪除重複資料最快寫法：DISTINCT INTO 暫存表法與選項啟用

資料表出現完全重複列時，最快清除方式是利用 SELECT DISTINCT INTO 建立無重複暫存表，清空原表再寫回。本文提供標準四步驟腳本、SELECT INTO 選項啟用方法與效能說明。

標準清除腳本

-- 1. 將不重複資料寫入暫存表
SELECT DISTINCT * INTO #Tmp_Table FROM SourceTable;

-- 2. 清空原表 (TRUNCATE 比 DELETE 快且不記錄逐列記錄)
TRUNCATE TABLE SourceTable;

-- 3. 寫回去重後資料
INSERT INTO SourceTable SELECT * FROM #Tmp_Table;

-- 4. 清理
DROP TABLE #Tmp_Table;

啟用 SELECT INTO 選項

預設資料庫選項 SELECT INTO/BULKCOPY 為關閉，需先啟用：

-- 方法 1：T-SQL
EXEC SP_DBOPTION 'YourDB', 'SELECT INTO/BULKCOPY', 'TRUE';

-- 方法 2：SSMS 圖形介面
-- 右鍵資料庫 → 屬性 → 選項 → 其他 → 勾選 "Select Into/Bulk Copy"

⚠️ SQL Server 2005+ 已移除 SP_DBOPTION，改用 ALTER DATABASE：

ALTER DATABASE YourDB SET RECOVERY BULK_LOGGED;  -- 間接允許最小記錄操作
-- 或直接啟用資料庫選項 (相容性層級 90+)
EXEC SP_CONFIGURE 'SHOW ADVANCED OPTIONS', 1; RECONFIGURE;
-- 實際上 2005+ 預設即允許 SELECT INTO，無需額外設定

進階：保留主鍵/身分識別欄位

-- 若表有 IDENTITY 欄位，需明確列出欄位清單
SELECT DISTINCT Col1, Col2, Col3 INTO #Tmp FROM SourceTable;
TRUNCATE TABLE SourceTable;
SET IDENTITY_INSERT SourceTable ON;
INSERT INTO SourceTable (Col1, Col2, Col3) SELECT Col1, Col2, Col3 FROM #Tmp;
SET IDENTITY_INSERT SourceTable OFF;

效能比較 (100 萬筆、10% 重複)

適用條件與限制

• ✅ 適用：完全重複列 (所有欄位值相同)。
• ❌ 不適用：需根據業務鍵判斷重複 (如同一訂單號出現多次，保留最新) — 改用 ROW_NUMBER()。
• ⚠️ 需足夠 TempDB 空間容納暫存表。
• ⚠️ 原表有外鍵參考時，TRUNCATE 會失敗，改用 DELETE 或暫停約束。