안녕하세요. 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 구문정보

TRUNCATE TABLE table_name


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


 - 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 쿼리가 있습니다.

DELETE FROM 회원테이블 WHERE 테이블고윳값 = 1

 

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

DELETE FROM 회원테이블

 

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


생각만 해도 끔찍합니다. 회사에서 가장 중요한 회원 데이터라면? 또 실수로 백업도 한 번도 안 해서 복구도 불가한 상황이라면?
이런 경우 트랜잭션(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 

책구매링크.png

No. Subject Author Date Views
Notice SQL강좌: 챗GPT와 함께 배우는 SQL Server 무료 강좌 목차와 소개 (2023년 9월 업데이트) 코난(김대우) 2023.08.18 3171
Notice Python 무료 강좌 - 기초, 중급, 머신러닝(2023년 6월 업데이트) 코난(김대우) 2021.01.01 1960
2254 SQL강좌: 6-2. 데이터 무결성 - 테이블 컬럼과 NULL 제약 코난(김대우) 2023.08.18 13
2253 SQL강좌: 6-1. 데이터 무결성 - 데이터 무결성 이해 file 코난(김대우) 2023.08.18 16
2252 SQL강좌: 5-4. 테이블 수정 file 코난(김대우) 2023.08.18 27
2251 SQL강좌: 5-3. 테이블 생성 file 코난(김대우) 2023.08.18 12
2250 SQL강좌: 5-2. 테이블 생성과 데이터형 선택 [1] 코난(김대우) 2023.08.18 9
2249 SQL강좌: 5-1. SQL Server 테이블 file 코난(김대우) 2023.08.18 16
2248 SQL강좌: 4-8. 시스템 카탈로그 엿보기 file 코난(김대우) 2023.08.18 12
2247 SQL강좌: 4-7. 최적의 데이터베이스 구성 file 코난(김대우) 2023.08.18 26
2246 SQL강좌: 4-6. 데이터베이스의 데이터와 로그 코난(김대우) 2023.08.18 6
2245 SQL강좌: 4-5. 데이터베이스 삭제 file 코난(김대우) 2023.08.18 13
2244 SQL강좌: 4-4. 데이터베이스 옵션 file 코난(김대우) 2023.08.18 22
2243 SQL강좌: 4-3. 데이터베이스 크기조절 file 코난(김대우) 2023.08.18 19
2242 SQL강좌: 4-2. 데이터베이스 생성 file 코난(김대우) 2023.08.18 18
2241 SQL강좌: 4-1. SQL Server 데이터베이스와 데이터베이스 개체의 이해 file 코난(김대우) 2023.08.18 33
» SQL강좌: 3-4. TRUNCATE TABLE / 트랜잭션 수행 [1] 코난(김대우) 2023.08.18 20
2239 SQL강좌: 3-3. DELETE를 이용한 로우 삭제 코난(김대우) 2023.08.18 14
2238 SQL강좌: 3-2. UPDATE - 데이터 수정 코난(김대우) 2023.08.18 11
2237 SQL강좌: 3-1. 데이터 삽입, 삭제, 수정 - INSERT를 이용한 행 삽입 코난(김대우) 2023.08.18 16
2236 SQL강좌: 2-14. SQL 쿼리 자동생성 - 쿼리 디자이너 file 코난(김대우) 2023.08.18 29
2235 SQL강좌: 2-13. SELECT 결과셋을 XML, JSON 형식으로 출력 코난(김대우) 2023.08.18 24





XE Login