正確刪除 SQL Server 表中的重復行的實際操作步驟
此文章主要是教會大家如何正確刪除 SQL Server 表中的重復行，我們大家都知道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.
本文討論如何查找和刪除 SQL Server 表中的重復的主鍵。但是，您應該仔細檢查出現重復鍵的進程以避免重復出現。
更多信息
在該示例中，我們將使用下表，它具有重復的 PK 值。在該表中，主鍵是兩列(col1、col2)。我們無法創建唯一索引或主鍵約束，因為這兩行具有重復的 PK。該過程演示如何識別和刪除 SQL Server 表重復的主鍵。
- 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 值，則逐個刪除 SQL Server 表中的重復行會很費時間。在這種情況下，可使用下面的方法： 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.從原始表中刪除 SQL Server 表中的重復行。例如：
- DELETE t1
- FROM t1, holdkey
- WHERE t1.col1 = holdkey.col1
- AND t1.col2 = holdkey.col2
6.將唯一行放回原始表中。例如：
- INSERT t1 SELECT * FROM holddups
上述的相關內容就是對刪除 SQL Server 表中的重復行的描述，希望會給你帶來一些幫助在此方面。
【編輯推薦】
