今天在客戶端發現資料庫中存留有一些重複的垃圾資料，因此想透過 T-SQL 指令直接刪除重複的資料，不過一般透過常見透過 GROUP BY 的方式刪除重複資料卻不適用於今天的狀況，因為我們資料表的主索引鍵 (Primary Key) 是 uniqueidentifier 類型，不能透過 MAX 彙總函示運算，這時就要利用 OVER 子句並搭配 PARTITION BY 語法才能幫我完成任務。
一般來說，最常見的刪除重複資料方法是類似如下的 T-SQL 語法：
DELETE FROM [dbo].[MyTable] WHERE 主索引鍵 NOT IN
(SELECT MAX(主索引鍵) From [dbo].[MyTable] GROUP BY 欄位1, 欄位2, 欄位3)
這個刪除重複資料的方法重點有二：
- 一定要有個數值類型的主索引鍵，例如：int
◆ 在上述語法中就是名為 ID 這個欄位
- 決定何謂重複資料
◆ 若判斷重複資料的依據是看其中 3 個欄位，那就將這些欄位全部列入 GROUP BY 子句中
不過，這樣一個解法無法適用於主索引鍵為 uniqueidentifier 類型的表格，因為 uniqueidentifier 類型的欄位無法使用 MAX 彙總函示取得重複資料的唯一值，因此就必須尋求其他彙總函示來運算，而我們的解法就是透過 SQL Server 2005 才提供的 一般資料表運算式 (Common Table Expression) (簡稱 CTE) 語法，加上 OVER 子句並搭配 PARTITION BY 語法來完成刪除重複資料的動作，以下是範例語法：
WITH TmpOrderdTable
AS
(
SELECT
GroupID = ROW_NUMBER() OVER (PARTITION BY 欄位1, 欄位2, 欄位3 ORDER BY 主索引鍵)
FROM
[dbo].[MyTable]
)
DELETE FROM TmpOrderdTable WHERE GroupID > 1
透過這種方式不管任意類型的主索引鍵都能成功刪除重複資料，缺點只在於只有 SQL Server 2005 以上的版本才支援此語法。
額外補充 1
如果發現刪除的時間非常久（如果刪除十萬筆資料超過 5 秒就算久），那很可能是已經發生資料鎖定的情況，這種狀況在忙碌的正式機刪除重複資料時經常發生，這時建議先將資料庫設定為「單人存取模式」再進行刪除重複資料的動作，範例語法如下：
USE [master]
ALTER DATABASE [MyDB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
USE [MyDB]
WITH TmpOrderdTable
AS (
SELECT GroupID = ROW_NUMBER() OVER (PARTITION BY 欄位1, 欄位2, 欄位3 ORDER BY 主索引鍵)
FROM [dbo].[MyTable]
)
DELETE FROM TmpOrderdTable WHERE GroupID > 1
USE [master]
ALTER DATABASE [MyDB] SET MULTI_USER WITH ROLLBACK IMMEDIATE
額外補充 2
若要避免資料重複，建議額外建立含有多個欄位的「唯一索引鍵」，從資料庫層級就阻擋所有可能重複資料的出現。
相關連結