如何刪除 SQL Server 表中的重複行

原文：http://support.microsoft.com/kb/139444/
概要
Microsoft SQL Server 表不應該包含重複行和非唯一主鍵。為簡潔起見，在本文中我們有時稱主鍵為“鍵”或“PK”，但這始終表示“主鍵”。重複的 PK 違反了實體完整性，在關係系統中是不允許的。SQL Server 有各種強制執行實體完整性的機制，包括索引、唯一約束、主鍵約束和觸發器。

儘管如此，在某些情況下還可能會出現重複的主鍵；如果出現此類情況，就必須清除重複主鍵。出現重複主鍵的情形之一是，在 SQL Server 外部的非關係資料中存在重複的 PK，在匯入這些資料時沒有強制執行 PK 唯一性。出現重複主鍵的另一種情形來自資料庫設計錯誤，如沒有對每張表強制執行實體完整性。

通常在嘗試建立唯一索引時會發現重複的 PK，因為如果找到重複的鍵，唯一索引的建立即會中止，並且將顯示以下訊息：
Msg 1505, Level 16, State 1 Create unique index aborted on duplicate key.
如果使用的是 SQL Server 2000 或 SQL Server 2005，則會收到以下錯誤訊息：
Msg 1505, Level 16, State 1 CREATE UNIQUE INDEX terminated because a duplicate key was found for object name '%.*ls' and index name '%.*ls'.The duplicate key value is %ls.
本文討論如何尋找和刪除表中重複的主鍵。但是，您應該仔細檢查出現重複鍵的進程以避免重複出現。

更多資訊
在該樣本中，我們將使用下表，它具有重複的 PK 值。在該表中，主鍵是兩列（col1、col2）。我們無法建立唯一索引或主鍵約束，因為這兩行具有重複的 PK。該過程示範如何識別和重複資料刪除的主鍵。
create table t1(col1 int, col2 int, col3 char(50))
insert into t1 values (1, 1, 'data value one')
insert into t1 values (1, 1, 'data value one')
insert into t1 values (1, 2, 'data value two')

第一步是識別哪些行具有重複的主索引值：
SELECT col1, col2, count(*)
FROM t1
GROUP BY col1, col2
HAVING count(*) > 1

這將為表中的每組重複的 PK 值返回一行。此結果中的最後一列是特定 PK 值的重複數。

col1 col2 
1 1 2

如果只有幾組重複的 PK 值，則最佳方法是手動逐個刪除它們。例如：
set rowcount 1
delete from t1
where col1=1 and col2=1

rowcount 值應該是給定索引值的重複數減去 1。在該樣本中，有 2 個重複的主鍵，所以 rowcount 被設定為 1。col1/col2 值來自上面的 GROUP BY 查詢結果。如果 GROUP BY 查詢返回多行，則“set rowcount”查詢將必須為這些行中的每一行各運行一次。每次運行該查詢時，將 rowcount 設定為特定 PK 值的重複數減去 1。

在刪除行之前，您應該驗證是否整行重複。雖然整行重複不太可能發生，但可能 PK 值重複，而整行不重複。例如一個將社會安全號碼碼作為主鍵的表，該表有兩個具有相同號碼的不同的人（即行），但每個人有唯一的屬性。在這種情況下，任何引起重複鍵的問題可能還引起在行中放入有效唯一的資料。在刪除該資料之前，應該將該資料複製出來並儲存下來以進行研究和適當的調整。

如果表中有多組完全不同的重複的 PK 值，則逐個刪除它們會很費時間。在這種情況下，可使用下面的方法：

1.首先，運行上面的 GROUP BY 查詢來確定有多少組重複的 PK 值及每組的重複數。

2.選擇重複的索引值放入暫存資料表中。例如：
SELECT col1, col2, col3=count(*)
INTO holdkey
FROM t1
GROUP BY col1, col2
HAVING count(*) > 1

3.選擇重複的行放入暫存資料表中，以清除進程中的重複值。例如：
SELECT DISTINCT t1.*
INTO holddups
FROM t1, holdkey
WHERE t1.col1 = holdkey.col1
AND t1.col2 = holdkey.col2

4.此時，holddups 表應有唯一的 PK；但是，如果 t1 有重複的 PK 而行唯一（如上面的 SSN 樣本），情況就不是這樣了。請驗證 holddups 中的各個鍵是否唯一，是否沒有鍵重複而行唯一的情況。如果是這樣，您必須停在該處，確定對於給定重複的索引值，您希望保留哪些行。例如，以下查詢：
SELECT col1, col2, count(*)
FROM holddups
GROUP BY col1, col2

應為各行返回計數 1。如果結果是 1，請繼續執行下面的步驟 5。如果不是 1，則存在鍵重複而行唯一的情況，且需要您決定要儲存哪些行。通常，這將需要捨棄行或為此行建立新的唯一的索引值。為 holddups 表中每個此種重複 PK 執行這兩個步驟之一.

5.從原始表中重複資料刪除的行。例如：
DELETE t1
FROM t1, holdkey
WHERE t1.col1 = holdkey.col1
AND t1.col2 = holdkey.col2

6.將唯一行放回原始表中。例如：
INSERT t1 SELECT * FROM holddups