스크립트를 사용하여 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 또는 유사한 메서드를 대신 사용해야 합니다.