[MSSQL/MySQL] Delete or Truncate?

 안녕하세요 코북입니다. 오늘은 delete와 truncate의 차이점에 대해서 알아보겠습니다. delete와 truncate는 둘 다 테이블에 있는 row(레코드)를 삭제할 때 사용하는 명령어입니다. 저는 처음에 특정 조건으로 row를 삭제할 때 delete를 사용하고, row를 전부 삭제하고 싶을 때는 turncate를 사용하는 줄 알았는데.. 헷ㅎ 

 

 먼저 두 명령어는 레코드를 삭제할 때 사용하는 언어로 쓰임이 같지만, 그 종류가 다릅니다.

 

 Delete는 DML (Data Manipulation Language), 데이터 조작어로 데이터베이스에 입력된 레코드(row)를 조작하고 가공하는 언어입니다. 조회, 수정, 삭제하는 언어로 select, insert, update, delete가 그 예시입니다.

 

 Truncate는 DDL (Data Definition Language), 데이터 정의어로 데이터베이스를 정의하는 언어입니다. 개체(table)를 만들고 변경, 삭제하는 create, alter, drop이 그 예시입니다.

 

 구문은 다음과 같습니다.

-- TRUNCATE문
TRUNCATE TABLE [테이블명];

-- DELETE문
DELETE FROM [테이블명] WHERE [조건];

 

 가장 먼저 보이는 차이점은 아마 WHERE 조건을 걸 수 있는지 여부일 것입니다. 그럼 조건을 걸 수 있는 DELETE가 상위 호환 아닌가?라고 생각할 수 있는데 TRUNCATE TABLE이 갖는 이점이 존재합니다.

 

MSDN에서는 두 명령어에 대해 이렇게 비교합니다.

출처 : https://docs.microsoft.com/ko-kr/sql/t-sql/statements/truncate-table-transact-sql?redirectedfrom=MSDN&view=sql-server-ver16

 

1. 트랜잭션 로그 공간을 덜 사용합니다.
 DELETE문은 행을 한 번에 하나씩 제거하고 삭제된 각 행에 대해 트랜잭션 로그에 항목을 기록합니다. 반면  TRUNCATE TABLE은 테이블의 데이터를 저장하는 데 사용되는 데이터 페이지의 할당을 취소하는 방식으로 데이터를 제거하며 페이지 할당 취소만을 트랜잭션 로그에 기록합니다.

 

2. 일반적으로 적은 수의 잠금이 사용됩니다.

행 잠금을 사용하여 DELETE 문을 실행하면 삭제를 위해 테이블의 각 행이 잠깁니다. TRUNCATE TABLE은 항상 테이블과 페이지를 잠그지만 각 행은 잠그지 않습니다.

 

3. 빈 페이지는 예외 없이 테이블에 남습니다. 
 DELETE 문이 실행된 후에도 테이블은 계속 빈 페이지를 포함할 수 있습니다. 예를 들어, 힙의 빈 페이지는 최소한 배타적인(LCK_M_X) 테이블 잠금이 있어야만 할당 취소할 수 있으므로 삭제를 위해 테이블 잠금을 사용하지 않는 경우 테이블(힙)에는 빈 페이지가 많이 남게 됩니다. 인덱스의 경우도 삭제 작업 후에 빈 페이지가 남을 수 있지만 이러한 페이지는 백그라운드 정리 프로세스에 의해 신속하게 할당 취소됩니다.

 

 다음은 상황에 따라 구분해서 사용해야 하는 경우입니다.

1. TRUNCATE TABLE은 테이블에서 모든 행을 제거하지만 테이블 구조와 테이블의 열, 제약 조건, 인덱스 등은 그대로 남습니다. 테이블 정의 및 테이블의 데이터를 제거하려면 DROP TABLE 문을 사용하세요.

2. 테이블에 ID 열이 포함되어 있으면 해당 열의 카운터는 열에 대한 초기값으로 다시 설정됩니다. 초기값이 정의되어 있지 않으면 기본값인 1이 사용됩니다. ID 카운터를 보존하려면 DELETE를 대신 사용하세요.

 

2번은 TRUNCATE TABLE 사용 시 자동 증가 값이 초기화된다는 말입니다. 자동 증가 값을 유지하려면 DELETE를 사용하시면 됩니다.

 

 

당연히 TRUNCATE TABLE의 단점도 존재합니다.

 

1. 먼저 앞서 말씀드린 대로 조건을 사용할 수 없다는 점이 있습니다.

 

2. TRUNCATE TABLE은 DDL이기 때문에 사용권한의 문제도 있습니다. TRUNCATE TABLE 권한은 테이블 소유자, sysadmin 고정 서버 역할 및 db_owner 및 db_ddladmin 고정 데이터베이스 역할의 기본 권한이며 위임할 수 없습니다.

 

3. TRUNCATE 사용의 제한사항도 존재합니다.

다음과 같은 테이블에서는 TRUNCATE TABLE을 사용할 수 없습니다.

• 외래 키 (FOREIGN KEY) 제약 조건에 의해 참조됩니다. 자신을 참조하는 외래 키가 있는 테이블을 잘라낼 수 있습니다.
• 인덱싱된 뷰에 참여합니다.
• 트랜잭션 복제 또는 병합 복제에 의해 게시됩니다.

다음과 같은 특징이 있는 경우 DELETE문을 사용해야 합니다. TRUNCATE TABLE은 개별 행 삭제를 기록하지 않기 때문에 트리거를 실행할 수 없습니다.

 

 

 정리해보면 다음과 같습니다.

1) DELETE

- WHERE 절을 사용하여 테이블에 있는 데이터를 하나하나 선택하여 제거하는 방식

- WHERE 절을 사용하지 않고 테이블의 모든 테이터를 삭제하더라도, 내부적으로는 한 줄 한 줄 일일이 제거하는 과정을 거침

- 처리속도가 늦고, 퍼포먼스에 좋지 않은 영향을 줄 수 있다.

- 원하는 데이터만 골라 삭제할 때에는 DELETE 사용 / 전체 데이터 삭제할 때는 TRUNCATE 사용

- 데이터를 삭제하더라도 데이터가 담겨있는 Storage는 Release 되지 않는다.

- DELETE 된 데이터는 COMMIT 명령어를 사용하기 전이라면, ROLLBACK 명령어를 통해 되돌릴 수 있다.

 

2) TRUNCATE

- 전체 테이터를 한 번에 삭제하는 방식

- 최초 생성되었을 당시의 Storage만 남기고, 데이터가 남겨있는 Storage는 Release 된다.

- TRUNCATE TABLE을 하면 CREATE TABLE을 한 직후의 상태와 같다.

- 자동 COMMIT이 되는 명령어이기 때문에, 이미 지운 데이터는 되돌릴 수 없다.

 

 

이 글은 아래 링크 내용을 참고하여 정리한 내용입니다.

https://docs.microsoft.com/ko-kr/sql/t-sql/statements/truncate-table-transact-sql?redirectedfrom=MSDN&view=sql-server-ver16

https://prinha.tistory.com/entry/SQL-DELETE-TRUNCATE-DROP-%EC%B0%A8%EC%9D%B4%EC%A0%90

https://cbw1030.tistory.com/71