SQL DELETE와 TRUNCATE – 데이터 삭제하기

데이터를 삭제하는 건 언제나 두근두근(?)한 일! 이번 글에서는 TRUNCATE와 DELETE 명령어에 대해 알아보겠다. 특히, BigQuery와 MSSQL에서 각각의 용법을 살펴보겠다. 또한, 어떤 비즈니스 케이스에서 TRUNCATE를 사용하고, 어떤 상황에서 DELETE를 사용하는지에 대해서도 논의해보자. 기본적인 SQL 명령어에 아직 익숙하지 않다면, SQL (1) 기본 명령어 – 데이터 조작의 기초를 먼저 살펴보자.

■ TRUNCATE와 DELETE란?

SQL의 TRUNCATE와 DELETE 명령어는 테이블에서 데이터를 삭제할 때 사용된다. 두 명령어 모두 데이터를 제거하는 기능을 하지만, 그 작동 방식과 사용 목적에 있어 중요한 차이점이 있다.

TRUNCATE란?

TRUNCATE 명령어는 테이블의 모든 행을 빠르게 삭제하는 데 사용된다. 이는 테이블을 초기화하거나 임시 데이터를 제거할 때 유용하다. TRUNCATE는 전체 테이블을 대상으로 하며, 조건을 지정할 수 없다.

DELETE란?

DELETE 명령어는 테이블에서 특정 조건에 맞는 행을 삭제할 때 사용된다. WHERE 절을 통해 삭제할 행을 지정할 수 있으며, 선택적으로 일부 데이터만 제거할 수 있다.

■ TRUNCATE와 DELETE의 차이점

1. 작동 방식

• TRUNCATE:
  • 모든 행 삭제: 테이블의 모든 데이터를 한 번에 삭제한다.
  • 빠른 속도: 개별 행을 삭제하지 않고 데이터 페이지를 해제하여 빠르게 작동한다. 데이터는 페이지(page)라는 기본 단위로 저장되는데 테이블의 행(row)들이 페이지 안에 저장된다고 보면 된다. 여러 페이지가 모여 테이블의 전체 데이터를 구성하기 되는데, TRUNCATE 를 실행하면, 테이블의 모든 데이터가 삭제되는 대신, 데이터 페이지 자체가 해제(deallocationo)된다.
  • 자동 증가 카운터 리셋: 테이블의 auto-increment 카운터가 초기화된다. auto-increment 는 테이블의 특정 열에 대해 자동으로 고유한 숫자 값을 생성해주는 기능이다. 예를 들어, 새로운 행이 삽입될 때마다 자동으로 숫자가 증가하여 부여된다.
    
    예를 들어, 다음의 테이블이 있다고 하자.

DELETE를 사용하여 ID = 2 (Bob)을 지우고, David이라는 유저를 추가한다면, 아래와 같이 ID는 4로 auto-increment가 리셋되지 않는다. 만약 TRUNCATE를 하고 David를 추가한다면, ID = 1, Name = David인 한 행을 가진 테이블이 된다.

• DELETE:
  • 조건부 삭제: WHERE 절을 사용하여 특정 조건에 맞는 행만 삭제할 수 있다.
  • 개별 삭제: 각 행을 하나씩 삭제하며, 로그에 기록된다.
  • 자동 증가 카운터 유지: auto-increment 카운터가 리셋되지 않는다.

2. 로그 기록 (Logging)

• TRUNCATE:
  • 최소한의 로그: 데이터 페이지의 할당 해제만 로그에 기록되며, 개별 행 삭제는 기록되지 않는다.
  • 복구 가능성: 트랜잭션 내에서 사용될 경우 롤백이 가능하지만, 트랜잭션 외에서는 복구할 수 없다.
• DELETE:
  • 상세한 로그: 삭제된 각 행이 로그에 기록되어, 복구 시 모든 삭제 작업을 추적할 수 있다.
  • 복구 가능성: 트랜잭션 내에서 사용될 경우 언제든지 롤백이 가능하다.

3. 성능 및 잠금

• TRUNCATE:
  • 빠른 성능: 대량의 데이터를 신속하게 삭제할 수 있다.
  • 테이블 잠금: 전체 테이블이 잠금 상태가 된다.
• DELETE:
  • 느린 성능: 대량의 데이터를 삭제할 경우 시간이 많이 소요된다.
  • 행 단위 잠금: 조건에 따라 개별 행을 잠금 상태로 만든다.

4. 트리거 (Triggers)

**트리거(Trigger)**는 특정 이벤트(예: INSERT, UPDATE, DELETE)가 발생할 때 자동으로 실행되는 데이터베이스 객체인데, 트리거는 데이터의 무결성을 유지하거나, 변경 로그를 기록하거나, 추가적인 비즈니스 로직을 실행하는 데 사용된다. 예를 들어, 고객 테이블에서 고객 정보를 삭제하면, 해당 고객이 주문한 모든 주문까지 모두 삭제하는 것.

-- 고객 삭제 시 관련 주문도 삭제하는 트리거 생성하는 예시
CREATE TRIGGER trg_AfterCustomerDelete ON Customers
AFTER DELETE
AS
BEGIN
    DELETE FROM Orders
    WHERE CustomerID IN (SELECT CustomerID FROM deleted);
END;

위의 trg_AfterCustomerDelete는 Customers 테이블에서 고객이 삭제될 때마다 자동으로 실행되며, 삭제된 고객(deleted 테이블)에 해당하는 모든 주문이 Orders 테이블에서 삭제되어 데이터 무결성을 유지할 수 있다.

• TRUNCATE:
  • 트리거 미작동: DELETE 트리거가 작동하지 않아, 삭제 시 추가 로직이 실행되지 않는다 (데이터 페이지를 해제하기 때문에 트리거 작동 기회가 없겠죠?).
• DELETE:
  • 트리거 작동: DELETE 트리거가 활성화되어 추가적인 로직을 자동으로 실행할 수 있다.

5. 권한 (Permissions)

• TRUNCATE:
  • 높은 권한 필요: 보통 ALTER 테이블 권한이 필요하다.
• DELETE:
  • 낮은 권한 필요: DELETE 권한만 있으면 된다.

■ TRUNCATE와 DELETE 사용법

BigQuery는 완전한 데이터 웨어하우스로, TRUNCATE 명령어 대신 DELETE 명령어를 사용하여 테이블의 모든 데이터를 삭제할 수 있다. BigQuery는 대규모 데이터 처리를 위해 최적화되어 있으므로, 전체 데이터를 삭제할 때도 효율적이다. MSSQL에서는 TRUNCATE와 DELETE 명령어를 모두 지원하며, 각자의 특성을 활용할 수 있다.

-- BigQuery 모든 행 삭제
DELETE FROM dataset.table_name WHERE TRUE;

-- BigQuery 특정 조건에 맞는 행 삭제
DELETE FROM dataset.table_name WHERE condition;

-- MSSQL TRUNCATE 사용 예시
TRUNCATE TABLE dbo.table_name;

-- MSSQL DELETE 사용 예시
DELETE FROM dbo.table_name WHERE condition;

■ 실제 비즈니스 사례

1. 데이터 초기화 및 임시 데이터 제거 (TRUNCATE 사용)

데이터 웨어하우스에서 주기적으로 임시 데이터를 저장하는 테이블을 사용하는 경우, 데이터를 초기화하여 저장 공간을 확보할 필요가 있다. 이때 TRUNCATE 명령어를 사용하면 빠르게 모든 데이터를 삭제할 수 있다.

-- 임시 데이터 테이블 초기화
TRUNCATE TABLE dbo.temp_table;

2. 특정 조건에 맞는 데이터 삭제 (DELETE 사용)

사용자 관리 시스템에서 비활성 사용자 계정을 삭제하거나, 특정 기간이 지난 로그 데이터를 제거할 때 DELETE 명령어를 사용한다. 이를 통해 필요한 데이터만 선택적으로 삭제할 수 있다.

-- 1년 이상 비활성 사용자 삭제
DELETE FROM dbo.users WHERE last_active < DATEADD(year, -1, GETDATE());

3. 데이터 마이그레이션 준비 (TRUNCATE 사용)

데이터를 클라우드로 마이그레이션하는 과정에서 기존 SQL Server의 데이터를 GCP로 성공적으로 복제한 후, SQL Server의 데이터를 정리할 때 TRUNCATE를 사용할 수 있다. 이는 데이터 페이지를 빠르게 해제하여 저장 공간을 회복하는 데 도움이 된다.

-- 데이터 마이그레이션 후 테이블 초기화
TRUNCATE TABLE dbo.prod_tracking_users;

4. 로그 데이터 관리 (DELETE 사용)

웹 애플리케이션의 로그 데이터를 관리할 때, 특정 조건에 맞는 오래된 로그만 삭제하여 데이터베이스의 크기를 관리할 수 있다.

-- 30일 이상 된 로그 삭제
DELETE FROM dbo.event_logs WHERE event_date < DATEADD(day, -30, GETDATE());

■ 결론

TRUNCATE와 DELETE는 데이터베이스에서 데이터를 삭제하는 데 사용되는 중요한 명령어이다. TRUNCATE는 전체 데이터를 빠르게 삭제하고 auto-increment 카운터를 초기화하는 데 유용하며, DELETE는 조건에 맞는 특정 데이터를 선택적으로 삭제하는 데 적합하다. BigQuery와 MSSQL에서 각각의 명령어를 적절히 활용하여 데이터 관리의 효율성을 높일 수 있다. 이 글이 비즈니스 요구사항에 따라 데이터베이스를 효과적으로 관리하는데 도움이 되었으면 한다.

관련 자료

• MSSQL 문서
• BigQuery 문서