안녕하세요. SQLER의 코난 김대우입니다. 
이번 강좌에서는, 3-4. TRUNCATE TABLE / 트랜잭션 수행을 진행 하겠습니다.

SQLER에서 진행되는, 챗GPT와 함께 배우는 SQL Server 강좌 목록

이번에 진행할 강좌는 데이터 수정 마지막 강좌 TRUNCATE TABLE입니다.

TL;DR

TRUNCATE TABLE 구문은 테이블의 모든 데이터를 삭제합니다. DELETE와 비교 시 TRUNCATE가 더 빠르고 로그를 적게 사용하며, 둘 다 데이터만 삭제하고 테이블 정의는 유지합니다.

TRUNCATE TABLE 구문 예제

테이블의 모든 데이터를 삭제하는 TRUNCATE TABLE 구문입니다. 먼저 예제를 수행합니다.

-- TRUNCATE TABLE 테스트 테이블 SELECT INTO로 복사
SELECT ProductID, Name, ListPrice 
INTO TruncateTest FROM Production.Product;
GO

-- TruncateTest 조회
SELECT * FROM TruncateTest;
GO

SELECT INTO로 일부 데이터가 복제되어 TruncateTest 테이블로 생성되었습니다. 이제 TRUNCATE 구문을 수행합니다.

-- TruncateTest 테이블 데이터 비우기
TRUNCATE TABLE TruncateTest;
GO

-- 빈 테이블 확인
SELECT * FROM TruncateTest;
GO

모든 데이터가 삭제되고 테이블이 비워졌습니다.

TRUNCATE TABLE 구문정보

테이블에 있는 모든 로우를 삭제합니다.

 - DELETE와는 달리 모든 변경 사항을 로그에 기록하지 않고 할당받았던 전체 데이터 페이지의 반납 상황만을 로그에 기록한다.
 - DELETE문 보다 빠르다.
 - 즉시 테이블을 위한 데이터와 색인을 위한 공간을 반환한다.
 - 테이블은 여전히 존재한다.

TRUNCATE와 DELETE 차이

DELETE FROM 테이블 구문으로 모든 데이터를 삭제하는 것과  TRUNCATE TABLE 구문의 차이는 무엇일까요?

- DELETE 구문은 로우 개별 건의 삭제를 트랜잭션 로그에 쓰고, TRUNCATE TABLE 작업은 조건에 따라 작업 종료만 로그에 써서 속도가 빠르고 트랜잭션 로그를 적게 사용합니다. 이후 트랜잭션 강좌에서 자세히 배웁니다. 

- DELETE는 로우단위 잠금을 수행하고 개별 로우를 삭제하지만, TRUNCATE TABLE은 테이블이나 페이지 단위 잠금 후 삭제합니다. 이후 잠금 강좌에서 배웁니다.

- 둘 다 테이블의 데이터는 삭제되지만, 색인(Index), 제약(Constraints) 등의 테이블 정의는 유지됩니다.

- TRUNCATE 작업도 DELETE 작업처럼 ROLLBACK이 가능합니다.(안 되는 것으로 아시는 분들도 많습니다.)

차이점이 더 복잡합니다.

지금은 두 방식에 약간의 차이가 있구나 정도만 기억하시면 됩니다. 이후 대규모 데이터 삭제나 동시사용량이 많은 데이터베이스를 다룰 경우에 두 가지 방식 중에서 신중하게 선택해 사용해야 합니다.

BEGIN TRAN / COMMIT TRAN / ROLLBACK TRAN

주의사항

이 작업은 실제 운영 서버에서는 하시면 안 되며 개인 개발이나 테스트 머신에서만 수행하세요. BEGIN TRAN을 함부로 수행하면 잠금(Lock)과 블로킹(Blocking)으로 다른 사용자의 쿼리가 대기할 수 있습니다. 반드시 개인 개발 SQL 서버에서만 사용하세요. 
운영 서버에서 사용하게 된다면 강좌에서 진행될 “트랜잭션 / 잠금의 범위”에 대해 충분히 배우고 연습하신 후 사용하세요.

실수로 테이블 모든 데이터를 삭제/수정

지난 UPDATE 강좌에서도 짧게 말씀드린 내용으로, UPDATE나 DELETE 구문에 WHERE절 없이 수정하는 실수를 미연에 방지하는 방법이 있습니다.

예를 들어, 다음과 같은 SQL 쿼리가 있습니다.

위의 쿼리는 WHERE절이 있는데 개발과 쿼리 작성 도중 실수로 아래처럼 수행해 버렸습니다.

중요한 데이터를 모두 삭제해 버렸다고 상상해 보세요. 

생각만 해도 끔찍합니다. 회사에서 가장 중요한 회원 데이터라면? 또 실수로 백업도 한 번도 안 해서 복구도 불가한 상황이라면?
이런 경우 트랜잭션(TRANSACTION)과 트랜잭션 격리 레벨(Transaction Isolation Level)을 사용할 수 있습니다. 이후 강좌에서 상세히 진행하고 여기서는 간략하게 소개합니다.

BEGIN TRAN / COMMIT TRAN / ROLLBACK TRAN

예제를 단계별로 실행합니다.

-- TransTest 테스트 테이블을 SELECT INTO로 복사해 생성
SELECT ProductID, Name, ListPrice 
INTO TransTest FROM Production.Product;
GO

-- TransTest 조회
SELECT * FROM TransTest
WHERE ListPrice > 3500;
GO

-- 트랜잭션 시작
BEGIN TRAN;

-- 실수로 WHERE 없이 전체 테이블 삭제 수행
DELETE FROM TransTest;
GO

-- 데이터 조회 후 상황을 확인
SELECT ProductID, Name, ListPrice 
FROM TransTest;
GO

-- ROLLBACK으로 처리 되돌리기 수행
ROLLBACK TRAN;

ROLLBACK 후 다시 데이터를 조회하면 실수한 DELETE 작업이 모두 롤백된 것을 확인할 수 있습니다.
그렇다면, 정상 처리로 트랜잭션을 완료하려면 어떻게 할까요? COMMIT TRAN을 하면 됩니다. 아래 예제를 단계별로 수행하세요.

--다시 트랜잭션을 열고 DELETE 수행 후 COMMIT으로 트랜잭션 완료
BEGIN TRAN;

-- 정상적인 DELETE 작업 수행
DELETE FROM TransTest
WHERE ListPrice > 3500;

SELECT ProductID, Name, ListPrice
FROM TransTest
WHERE ListPrice > 3500;

-- 데이터 확인 후 COMMIT TRAN으로 트랜잭션 완료
COMMIT TRAN;

이렇게 중요한 데이터를 수정하기 전에 꼭 BEGIN TRAN을 수행하고 제대로 완료되면 COMMIT TRAN, 실수라면? ROLLBACK TRAN 수행하세요.

주의사항

이 작업은 실제 운영 서버에서는 하시면 안 되며 개인 개발이나 테스트 머신에서만 수행하세요. BEGIN TRAN을 함부로 수행하면 잠금(Lock)과 블로킹(Blocking)으로 다른 사용자의 쿼리가 대기할 수 있습니다. 반드시 개인 개발 SQL 서버에서만 사용하세요. 
운영 서버에서 사용하게 된다면 강좌에서 진행될 “트랜잭션 / 잠금의 범위”에 대해 충분히 배우고 연습하신 후 사용하세요.

SQL 강좌 책 구매

강좌가 도움이 되셨다면, 책으로 구매 가능합니다. 책 판매 수익금은 전액 코딩 교육 사회공헌 활동에 기부되며, 아래 링크에서 구매하시면 더 많은 금액이 기부됩니다. 

책구매 링크: 챗GPT와 함께하는 마이크로소프트 SQL Server 2022