スクリプトを使用して SQL Server テーブルから重複する行を削除する

この記事では、Microsoft SQL Server のテーブルから重複する行を削除するために使用できるスクリプトについて説明します。

元の製品バージョン:   SQL Server
元の KB 番号:   70956

概要

SQL Server テーブルから重複するレコードを削除するために使用できる一般的な方法は 2 つあります。 デモを行う場合は、まずサンプル テーブルとデータを作成します。

create table original_table (key_value int )

insert into original_table values (1)
insert into original_table values (1)
insert into original_table values (1)

insert into original_table values (2)
insert into original_table values (2)
insert into original_table values (2)
insert into original_table values (2)

次に、次の方法を試して、テーブルから重複する行を削除します。

方法 1

次のスクリプトを実行します。

SELECT DISTINCT *
INTO duplicate_table
FROM original_table
GROUP BY key_value
HAVING COUNT(key_value) > 1

DELETE original_table
WHERE key_value
IN (SELECT key_value
FROM duplicate_table)

INSERT original_table
SELECT *
FROM duplicate_table

DROP TABLE duplicate_table

このスクリプトでは、次のアクションを指定された順序で実行します。

  • 元のテーブル内の重複する行の 1 つのインスタンスを、重複するテーブルに移動します。
  • 重複するテーブル内にある元のテーブルからすべての行を削除します。
  • 重複するテーブル内の行を元のテーブルに戻します。
  • 重複するテーブルを削除します。

この方法は簡単です。 ただし、重複するテーブルを一時的にビルドするには、データベースで十分な領域が必要です。 この方法は、データを移動するため、オーバーヘッドも発生します。

また、テーブルに IDENTITY 列がある場合は、元のテーブルにデータを復元するときに SET IDENTITY_INSERT ON を使用する必要があります。

方法 2

Microsoft SQL Server 2005 で導入された ROW_NUMBER 関数を使用すると、この操作がはるかに簡単になります。

DELETE T
FROM
(
SELECT *
, DupRank = ROW_NUMBER() OVER (
              PARTITION BY key_value
              ORDER BY (SELECT NULL)
            )
FROM original_table
) AS T
WHERE DupRank > 1 

このスクリプトでは、次のアクションを指定された順序で実行します。

  • ROW_NUMBER 関数を使用して、コンマで区切られた 1 つ以上の列である key_value に基づいてデータを分割します。
  • 1 より大きい DupRank 値を受け取ったすべてのレコードを削除します。 この値は、レコードが重複していることを示します。

(SELECT NULL) 式のため、スクリプトは、条件に基づいてパーティション分割されたデータを並べ替えません。 重複を削除するロジックで、削除するレコードと、他の列の並べ替え順序に基づいて保持するレコードを選択する必要がある場合は、ORDER BY 式を使用してこれを行うことができます。

詳細

方法 2 は、次の理由からシンプルで効果的です。

  • 重複するレコードを別のテーブルに一時的にコピーする必要はありません。
  • 元のテーブルをそれ自体と結合する必要はありません (たとえば、GROUP BY と HAVING の組み合わせを使用して重複するすべてのレコードを返すサブクエリを使用)。
  • 最高のパフォーマンスを得るには、key_value をインデックス キーとして使用し、ORDER BY 式で使用した可能性のある並べ替え列を含む、対応するインデックスをテーブルに配置する必要があります。

ただし、この方法は、ROW_NUMBER 関数をサポートしていない古いバージョンの SQL Server では機能しません。 このような場合は、代わりに方法 1 または同様の方法を使用する必要があります。