스크립트를 사용하여 SQL Server 테이블에서 중복 행 제거

이 문서에서는 Microsoft SQL Server 테이블에서 중복 행을 제거하는 데 사용할 수 있는 스크립트를 제공합니다.

원래 제품 버전: SQL Server
원본 KB 번호: 70956

요약

SQL Server 테이블에서 중복 레코드를 삭제하는 데 사용할 수 있는 두 가지 일반적인 방법이 있습니다. 데모의 경우 먼저 샘플 테이블 및 데이터를 만듭니다.

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

이 스크립트에서는 다음 방법을 제시된 순서대로 진행합니다.

  • 원본 테이블의 중복 행 인스턴스 하나를 중복 테이블로 이동합니다.
  • 중복 테이블에 있는 원본 테이블에서 모든 행을 삭제합니다.
  • 중복 테이블의 행을 원본 테이블로 다시 이동합니다.
  • 중복 테이블을 삭제합니다.

이 메서드는 간단합니다. 그러나 데이터베이스에서 중복 테이블을 임시로 빌드할 수 있는 충분한 공간이 있어야 합니다. 또한 이 메서드는 데이터를 이동하기 때문에 오버헤드가 발생합니다.

또한 테이블에 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 함수를 사용하여 쉼표로 구분된 하나 이상의 열이 될 수 있는 key_value에 따라 데이터를 분할합니다.
  • 1보다 큰 DupRank 값을 받은 모든 레코드를 삭제합니다. 이 값은 레코드가 중복되었음을 나타냅니다.

식 때문에 (SELECT NULL) 스크립트는 조건에 따라 분할된 데이터를 정렬하지 않습니다. 중복을 삭제하는 논리에서 삭제할 레코드와 다른 열의 정렬 순서에 따라 유지할 레코드를 선택해야 하는 경우 식을 사용하여 ORDER BY 이 작업을 수행할 수 있습니다.

추가 정보

메서드 2 는 다음과 같은 이유로 간단하고 효과적입니다.

  • 중복 레코드를 다른 테이블에 일시적으로 복사할 필요는 없습니다.
  • 원래 테이블을 자체와 조인할 필요는 없습니다(예: 및 HAVING의 조합을 GROUP BY 사용하여 모든 중복 레코드를 반환하는 하위 쿼리 사용).
  • 최상의 성능을 위해 를 인덱스 키로 사용하고 key_value 식에서 사용 ORDER BY 했을 수 있는 정렬 열을 포함하는 해당 인덱스가 테이블에 있어야 합니다.

그러나 이 메서드는 ROW_NUMBER 함수를 지원하지 않는 오래된 버전의 SQL Server 작동하지 않습니다. 이 경우 메서드 1 또는 유사한 메서드를 대신 사용해야 합니다.